Synchronized Combo Boxes

[Theresa]

Hi:

I have a form which contains a subform. On the subform I have a combo box,
cboRepl_Fixture, which looks up into the Replacement_Fixture_tbl. I have
another combo box in this same subform, cboLamp, that I would like to only
show the lamps available for that fixture.

I have the following code in the AfterUpdate Event of cboRepl_Fixture:

Private Sub cboRepl_Fixture_AfterUpdate()
Me.cboLamp.RowSource = "SELECT Lamp FROM" & _
" Replacement_Lamp_tbl WHERE Repl_Fixture_ID = " &
Me.cboRepl_Fixture & _
" ORDER BY Lamp"
Me.cboLamp = Me.cboLamp.ItemData(0)
End Sub


When I view the subform, cboLamp is empty.

Any suggestions would be appreciated.

 

[Tom Wickerath]

Hi Theresa,

Is the field [Repl_Fixture_ID] a numeric or text data type? As written, your
rowsource is valid only for a numeric data type.

Also, since you mention synchronized combo boxes on a subform, you might
want to have a look at the information that I show on page 13 of a Word
document that I call "Access Links". You are welcome to download a zipped
copy from my web site (link shown below).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Theresa]

Hi Tom:

Thanks for the articles. I am sure they will come in handy in the future.
Having read the associated links, I realized that what they are talking about
is not my current issues, although I could certainly see it happening if I
get to work in the first place. My current issue is that when I select the
drop down list for the second combo box, the list is empty. The
[Repl_Fixture_ID] is a numberic field and I used the coding and instructions
from the Northwind Sample Database.

Any ideas on what I'm doing wrong?

Thanks,

Hi Theresa,

Is the field [Repl_Fixture_ID] a numeric or text data type? As written, your
rowsource is valid only for a numeric data type.

Also, since you mention synchronized combo boxes on a subform, you might
want to have a look at the information that I show on page 13 of a Word
document that I call "Access Links". You are welcome to download a zipped
copy from my web site (link shown below).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Hi:

I have a form which contains a subform. On the subform I have a combo box,
cboRepl_Fixture, which looks up into the Replacement_Fixture_tbl. I have
another combo box in this same subform, cboLamp, that I would like to only
show the lamps available for that fixture.

I have the following code in the AfterUpdate Event of cboRepl_Fixture:

Private Sub cboRepl_Fixture_AfterUpdate()
Me.cboLamp.RowSource = "SELECT Lamp FROM" & _
" Replacement_Lamp_tbl WHERE Repl_Fixture_ID = " &
Me.cboRepl_Fixture & _
" ORDER BY Lamp"
Me.cboLamp = Me.cboLamp.ItemData(0)
End Sub


When I view the subform, cboLamp is empty.

Any suggestions would be appreciated.

 

[Tom Wickerath]

Hi Theresa,

My current issue is that when I select the drop down list for the
second combo box, the list is empty.

Try the following modifications to your procedure. Make sure that you have
those two very important words at the top of your form's class module: Option
Explicit. If you do not, see one of the hyperlinks in red font, on page 4 of
Access Links.doc. After replacing your existing procedure with the version I
show below, save the changes to the module, and then do a Debug | Compile
{ProjectName}. If you find any compile-time errors in your VBA project, fix
those first.

Now, open the form in normal mode. Make a selection from your
cboRepl_Fixture combo box to cause the AfterUpdate event procedure to run.
Press the <Ctrl> and <G> keys together, at the same time, to open the
Immediate Window. You should see the strSQL statement printed to the
Immediate Window. Does anything look obviously wrong with it? If not, copy
this statement into your clipboard by selecting it and tapping <Ctrl><C>.
Then, create a new query. Dismiss the Add Tables dialog. Click on View | SQL
View. Replace the default SELECT keyword with the text that you copied into
your clipboard. Attempt to run the query. If you get an error, make an
attempt to switch back into the query design view that displays the QBE
(Query By Example) grid.

If no records are returned, then this would explain why the second combo box
is empty. You need to adjust the SQL (Structured Query Language) statement
such that it returns the expected records.

Option Compare Database
Option Explicit

Private Sub cboRepl_Fixture_AfterUpdate()
On Error GoTo ProcError

Dim strSQL As String
strSQL = "SELECT Lamp FROM Replacement_Lamp_tbl " _
& "WHERE Repl_Fixture_ID = " & Me.cboRepl_Fixture & " " _
& "ORDER BY Lamp"

Debug.Print strSQL

Me.cboLamp.RowSource = strSQL
Me.cboLamp = Me.cboLamp.ItemData(0)

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cboRepl_Fixture_AfterUpdate..."
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Hi Tom:

Thanks for the articles. I am sure they will come in handy in the future.
Having read the associated links, I realized that what they are talking about
is not my current issues, although I could certainly see it happening if I
get to work in the first place. My current issue is that when I select the
drop down list for the second combo box, the list is empty. The
[Repl_Fixture_ID] is a numberic field and I used the coding and instructions
from the Northwind Sample Database.

Any ideas on what I'm doing wrong?

Thanks