Open 1 of 2 Forms based on Criteria

[DCV0204]

I need some help.


Within an MS Access database I want to click a button on my switchboard (already created) that will open a form named “Form1” or “Form2” depending if a where statement (using a pop-up box for criteria) is True.
For example, if the pop-up box were to ask for a “MemberID” and I typed in ‘1234567’, if the “MemberID” is in “Form1” then “Form1 would open; otherwise “Form2” would open.
Thanks.

[Robeen]

Hi DCV,

Where does the data that these two forms display come from?
A Table? Two Tables? Queries?

It is definitely possible to determine which form to open based on the value of MemberID.

I'm assuming that each Form is connected to a different range of MemberIDs - is that correct?

[DCV0204]

I have Form1 linked to a table with primary key "MemberID" (containing data elements that will always stay the same) and a subform within Form1 linked to another table containing the "MemberID" and a different Primary Key" where all other data elements will change. The "MemberID" will be the same in both tables.

Form1 and Form2 are pointed to the same data; it is just one is for a new member to be added and the other is for only the subform information to be updated (Mainform information is locked). There may be a better way to handle in one form, I do not know.

I hope this is not confusing.
DCV

[Robeen]

It's a little confusing - but tell me if I understand correctly.

Form1 is your Main Form.
Form2 is your SubForm.

Is that correct?

You say:

Form1 and Form2 are pointed to the same data

BUT you also say:

a subform within Form1 linked to another table

[DCV0204]

No, Both forms are the same.

Form1 will be used to add additional records to the subform for established members. Where data elements that will always stay the same are locked and can not be changed (ie., name, address).
and
Form2 is for new members where we will have to add all data.

I am wanting it to work like this:
When I click the button on my Switchboard (which currently I have opening Form1), I would like a pop-up to ask for MemberID (searching Table1) if the MemberID exist open Form1, if null or does not exist to open Form2 (which opens a blank form where a new member can be added.)

I hope I have not confused you more.
Thanks,
DCV

[Robeen]

Ok - now I think I understand.

I think you should research the DLookup function.

When you enter your MemberID and click your button on the popup - you should have VBA code in the Click Event of the button that says something like this:

 
Dim MemID as Long   'Assuming your MemberID is a number . . .
 
MemID = Me.txtMemberID.Value
 
If DLookup("FieldName", "TableName", "MemberID = " & Nz(MemID,0)) = 0 Then
' FieldName is 0 because the MemberID doesn't exist.
DoCmd.OpenForm "Form2"
Else
' FieldName is not 0 because the MemberID does exist.
DoCmd.OpenForm "Form1"
End If

I haven't tested this code but put it up to give you a start.

Let me know if you have problems with it.

[Robeen]

Try this for the DLookup statement:

 
If IsNull(DLookup("FieldName", "TableName", "MemberID = " & Nz(MemID,0))) Then

. . . sorry - I wasn't thinking . . .

[DCV0204]

If my MemberID is text would I use Char instead of Long?
Also, is Me in Me.txtMemberID.Value suppose to be the table name?

What should I put in place of FieldName in this statement?
If IsNull(DLookup("FieldName", "TableName", "MemberID = " & Nz(MemID,0))) Then

I am new to sql so please forgive me.

[Robeen]

You would declare it like this:
Dim MemID as String

Get Value from Your MemberID textbox on the Form:
MemID = Me.txtMemberID

and your DLookup would be like this [watch out for the single & double ticks]:
If IsNull(DLookup("FieldName", "TableName", "MemberID = '" & MemID & "'")) Then

In the FieldName above, you would put the Name of any field in the Table for which you would always have data if the MemberID existed.

The DLookup is saying:
Give me the value that is In FieldName [a field in your table], From TableName, Where MemberID = the value that is in MemID [which comes from the value on the form in txtMemberID.

Hope this helps.

[DCV0204]

I have pasted and changed the information I believe correctly but I am received an error.
I have attached a print screen of what I have and the error message
Thanks,
DCV

[Robeen]

I have never used 'Charc' in Access.
Did you try String?

[DCV0204]

I have tried this:
Dim MedID As String
' Get Value from Medicaid_ID textbox on the Form:
MedID = Me.txtMedicaid_ID.Text

If IsNull(DLookup("NAME", "Master_Facility_List_Table", "Medicaid_ID = " & Nz(MedID, 0))) Then
' NAME is 0 because the Medicaid_ID doesn't exist.
DoCmd.OpenForm "Form2"
Else
' NAME is not 0 because the Medicaid_ID does exist.
DoCmd.OpenForm "Form1"

and it is still giving me the compile error. It is getting hung up on Me, should I change Me to something else?
I am entering the information in the properties (On Open) in the form. Is this correct?

[Robeen]

1.
You said your MedID is a String - and soo I told you your DLookup statement should be :

 
If IsNull(DLookup("FieldName", "TableName", "MemberID = '" & MemID & "'")) Then

I took out the Nz function and changed the punctuation.

2.
I was actually thinking that this code would be in the On Click Event of a button on the Form on which you are entering the MedID number.

How is the Code going to get the value from:
Me.txtMedicaid_ID.Text
. . . if the Form is still opening?
I don't really 'know' but it's a thought.

I think you should let your Form open, enter your ID into txtMedicaid_ID - and then have a button to execute the code [in the On Click event] to open the form based on whether the ID exists or not.

I'm still curious how you would get your MedID if the Form is not open yet and the ID hasn't been entered on it.


Not sure if I'm missing something . . .

Let me know.

[DCV0204]

I can not get the code to go past Me in MedID = Me.Medicaid_ID.Text
statement when I hit run.

I am going to try and give all information here:
I created a switchboard that has a button to open Form1 ("INPUT_Data (NewFacility)", that is linked to "Master_Facility_List_Table")
"Master_Facility_List_Table" contains the data that will never change (ie., Medicaid_ID, demographic, Name).
Within Form1, there is a subform linked to "CandT_Information" table; which contains information that will change periodically.

In the properties area for Form1 in the OnOpen action is where I am wanting to put the search (if there is a better place I am up for
suggestions.). I thought that if Form1 was open which contains current data the search would work.

If the Medicaid_ID is not in Form1, then open Form2.

Here is the last code I tried:
Option Compare Database
Private Command0_Open()
Dim MedID As String
' Get Value from Medicaid_ID textbox on the Form:
MedID = Me.Medicaid_ID.Text
If IsNull(DLookup("NAME", "Master_Facility_List_Table", "Medicaid_ID = '" & MedID & "'")) Then
' NAME is 0 because the Medicaid_ID doesn't exist.
DoCmd.OpenForm "INPUT_Data (NewFacility)"
Else
' NAME is not 0 because the Medicaid_ID does exist.
DoCmd.OpenForm "INPUT_Data (ExistingFacility)"
End If
End Sub

I appreciate all of your help.

[Robeen]

Any chance you can post your DB [or a scaled-down version of it] here?