Opening new form with specific item in drop down box

[ScottBeatty]

Hey everyone, I am wondering if someone could help me. I have a master form for lack of a better phrase and 3 other forms that represent specific items in the original drop down box. In the master drop down box, I have all 50 states, and the 3 forms are for 3 specific states. Here's the code I have so far but when I save, it says "The 'OpenForm' macro action has an invalid value for the 'Where Condition' agrument."

Option Compare Database
Option Explicit
Private Sub State_AfterUpdate()
Select Case Me.State
Case "CA"
DoCmd.OpenForm "Auditor Form (CA)"

Case "MA"
DoCmd.OpenForm "Auditor Form (MA)"
Case "GA"
DoCmd.OpenForm "Auditor Form (GA)"
Case Else
'-- Do nothing
End Select
End Sub
Private Sub Combo100_AfterUpdate()
End Sub

Any help would be great! Thanks!

 

[joeKra]

i dont see here any Where syntax

 

[ScottBeatty]

I believe you. Do you have any suggestions though? I'm very unfamiliar with coding. The only reason I have what I have is from a thread exploring a similar topic but it did not help much apparently.

 

[joeKra]

please explain what you trying to do and i will try to leap you ....

 

[joeKra]

Oh.. i forgot to welcome you to the forum ....

 

[ScottBeatty]

In my drop down box, I have all 50 states. I have a form with about 20 yes or no boxes following that.

If CA is selected, I wanted it to open the CA form as a new record
If MA is selected, I wanted it to open the MA form as a new record
If GA is selected, I wanted it to open the GA form as a new record

Well if it easier, if CA is selected, I want to open up 3 more drop down boxes to the original form as well as MA and GA

However if the other 47 states are selected, then I want to remain on that form and not add any additional drop down boxes.

 

[joeKra]

let me see if i got you...
you have a form which you want to add records based on the state selected besides, but if the selected one is 1 of these states you want to be able to add additional information...
now, do you have for each state a separate table ? if yes then its wrong designed
what you need to do is, to add this 3 dropDown's and if 1 of these 3 is selected set the visible to true otherwise false, make sure before setting the visible property that they should not have the focus, so no new form is required to open you can have everything done in the same form \ table

 

[ScottBeatty]

Okay I follow that. How would I make the visibility enabled for 1/50 states though?

 

[joeKra]

on the Afterupdate event of the comboBox
if value = "GA" or... then
dropdown.visible = true
else
dropdown.visible = false
end if

 

[ScottBeatty]

I have a new one for you: I wanted to change the visibility to off because I don't want the additional information to show unless the state is selected but using the code you gave me earlier along with the visibility off, the drop down box remains invisible and I was wondering if you could give me some pointers. Thanks!

 

[joeKra]

sorry for delay....
please paste in you code

 

[ScottBeatty]

Private Sub Combo83_AfterUpdate()
If Value = "CA" Then
Dropdown.Visible = True
Else
Dropdown.Visible = False
End If
End Sub


This is just for one of the dropdown boxes but I've been using this same one for all of the additional dropdown boxes but just changing the "if value"

 

[joeKra]

IF Combo83 ="CA" or Combo83 = "GA" then
dropdown[replace with actual name].visible = true
else
Dropdown.Visible = False
End If

 

[ScottBeatty]

Private Sub Combo83_BeforeUpdate(Cancel As Integer)
If Combo125 = "CA" Then
Dropdown [Combo83].Visible = True
Else
Dropdown [Combo83].Visible = False
End If

End Sub

This is what I put it and it still did not change anything. I put 125 instead of 83 because that is the box that determines if 83 becomes visible or not.

 

[joeKra]

the code should be on the Combo125 not on the combo83, telling access that after selecting the city from Combo125 should apply visibility from combo83 according

 

[ScottBeatty]

well I want the visibility of Combo83 to depend on the answer provided in Combo125. Is it still the same code?

 

[joeKra]

you need to put the code under the afterupdate of the Combo125

 

[ScottBeatty]

Okay I came up with an error: Compile error sub or function not defined. The following are my first 2 sections of my code and I just continued that for all 11 drop down boxes. Side note: thank you so much so far. You must be a very patient person haha

Private Sub Combo125_AfterUpdate()
If Combo125 = "CA" Then
Dropdown [Combo83].Visible = True
Else
Dropdown [Combo83].Visible = False
End If
End Sub
Private Sub combo85_afterupdate()
If Combo125 = "CA" Then
Dropdown [Combo85].Visible = True
Else
Dropdown [Combo85].Visible = False
End If
End Sub

 

[joeKra]

don't tell me that this is your original code....
Dropdown is not a part of the code, that was only a guidance how to implement the dropdown the code should be like this:

also, i am a little confused do you have to combo's from where you choosing the state?
Combo125?
combo85?

Private Sub Combo125_AfterUpdate()
If Combo125 = "CA" Then
[Combo83].Visible = True
Else
[Combo83].Visible = False
End If
End Sub
Private Sub combo85_afterupdate()
If Combo125 = "CA" Then
[Combo85].Visible = True
Else
[Combo85].Visible = False
End If
End Sub

 

[ScottBeatty]

Oh, wow. Okay that's helpful for sure. I only have states being pulled from combo 125 and then additional information is dependant on what is being pulled from that combo box. However, I'm still getting an error message and was wondering if you could detect it.

The expression After Update you entered as the event property setting produced the following error: Ambiguous name detected: combo125_afterupdate