Open 1 of 2 Forms based on Criteria

[DCV0204]

Ok I have attached the dbase.


Thanks.

[Robeen]

my winzip fre trial expired and my company policy is not allowing me to download another zip software . . .

Any chance you can strip a copy of your db down to the bare essentials so you can post the stripped-down version here without having to zip it?

Sorry!

[DCV0204]

Ok, try this one.

[Robeen]

which form do you enter the new ID on??

[DCV0204]

New "INPUT_Data (NewFacility)"
Existing "INPUT_Data (ExistingFacility)"

[Robeen]

Ok. Let's start again.

I just read through this whole thread to see where I got off track.

I was under the impression that:
1. You want a popup window to appear when you click the button on the Switchboard.
2. You already have the Popup window [Form] designed and that it opens when you click that button.

I'm looking all over for where you've put the code sample I gave you but I can't find it.

You're using Code Macros to do your work.
I was talking about using the 'Code Builder' to write code to do your work for you.

I'm not very familliar with using embedded macros - because I found it limiting when I knew what I needed to do but couldn't find a macro action - or combination of macro actions to achieve it.

I'm not sure how to advise you if you want to go down the embedded macro road - simply because I have not used embedded macros.

Where are you typing in the MemID that you want to search for in your Table?

I thought you were typing in a MemberID [MedicaidID] into a TextBox on a Form and then - if that ID is already in your Table - opening up one form - and if it is not - opening up a different form.

Isn't that your goal?

If so - which Form is it that you type in that number to determine if it is already in your system or not?

[DCV0204]

I was entering the code in the properties area for Form1 in the OnOpen action by clicking the build button. I do not have to use a macro.

I may have deleted prior to sending you to copy, since it did not work for me.

[Robeen]

There's no Form1 in the db you sent me.

[DCV0204]

Sorry Form1 was the generic name I was using instead of the actual name.
Form2 = "INPUT_Data (NewFacility)"
Form1 = "INPUT_Data (ExistingFacility)"

[Robeen]

So . . . back to my other question:

Doesn't there have to be another form - into which you type your ID - so that your program can determine whether to open Form1 or Form2?

[DCV0204]

So are you saying I need to create another form linked to my main table ("Master_Facility_List_Table") to be a search form and have the code determine whether to open Form1 = "INPUT_Data (ExistingFacility)" or Form2 = "INPUT_Data (NewFacility)"?

If the answer is yes, would the form need to only containt the Medicaid_ID as the search field or would I also need the Name field since it is mentioned in the code?

[Robeen]

Yes - create another Form.
It doesn't have to be linked to any Table.

Create a Form in Design View [Create -> Form Design].
Add a Text Box [name it MedicaidID].
Add a Command Button [if the command button wizard opens - click Cancel].

Then:
Select the Button.
Open the Property Sheet and then Event tab.
Click the Ellipsis [...] to the right of On Click.
Choose 'Code Builder' [the VBA Editor will open].
Between the two lines in the VBA Editor:

 
Private Sub Command0_Click()
'Code will be in here . . . 
 
End Sub

is where you will put the code example I was discussing with you earlier in the thread. When you run the Form and click the button - the code in there will execute.

When you've got it working, clicking the button on this form will take the ID you enter into the text box on the Form, find out if it already exists in your Table and then accordingly open Form1 or Form2.

I hope this helps!

[DCV0204]

Ok, I created the form with the textbox and button. Adding the following code to the on click:

Private Sub Command3_Click()
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 = '" & Medicaid_ID & "'")) 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 am now getting a
runtime error '2185':
You can't reference a property or method for a control unless the
control has the focus.

[Robeen]

You need to 'SetFocus' to the text box before you can reference what is in it.
Also - you had a syntax error.
My changes are in red.

Private Sub Command3_Click()
Dim MedID As String
' Get Value from Medicaid_ID textbox on the Form:
Me.Medicaid_ID.SetFocus
MedID = Me.Medicaid_ID.Text
 
If IsNull(DLookup("NAME", "Master_Facility_List_Table", "Medicaid_ID = '" & MedID & "'")) Then
' NAME is Null because the Medicaid_ID doesn't exist.
 
DoCmd.OpenForm "INPUT_Data (NewFacility)"
 
Else
' NAME is not Null because the Medicaid_ID does exist.
DoCmd.OpenForm "INPUT_Data (ExistingFacility)"
 
End If
 
End Sub

Try that and see if it works.