Combo Box Update Question

[Sandy]

I have a form with two combo boxes. The second one is dependent on the value
in the first. It all works fine other than if the user moves to an existing
record, the value on the underlying query does not show in the comboBox2.

The code used for providing the data in comboBox2 is :-

Private Sub comboBox1_AfterUpdate()
On Error Resume Next
Me.comboBox2.RowSource = "SELECT tblRepairType.RepairTypeID,
tblRepairType.RepairType FROM" & _
" tblRepairType WHERE RepairCategoryID = " & Me.cboRepairCategoryID
& _
" ORDER BY RepairType"
Me.comboBox2 = Me.comboBox2.ItemData(0)
Me.comboBox2.Requery
End Sub

Any ideas?
Sandy

 

[Arvin Meyer [MVP]]

The data in combo2 relies upon the data in cboRepairCategoryID, not the
form. When you move to an existing record, you need to change the rowsource
to look at the form. Something like (psuedo code):

Sub Form_Current()
If Me.NewRecord = False Then
Use the existing rowsource
Else
Use the new rowsource
End If
End Sub

 

[Sandy]

Arvin

I have included the following code for my form :-

Private Sub Form_Current()
If Me.NewRecord = False Then
Me.cboRepairTypeID.RowSource = "SELECT tblRepairType.RepairTypeID,
tblRepairType.RepairType FROM" & _
" tblRepairType WHERE RepairCategoryID = " &
Me.cboRepairCategoryID & _
" ORDER BY RepairType"

Me.cboRepairTypeID = Me.cboRepairTypeID.ItemData(0)
Me.cboRepairTypeID.Requery
Else
Me.cboRepairTypeID.RowSource = "Select tblRepairJob.RepairTypeID
FROM" & _
" tblRepairJob WHERE JobNo = " & Me.txtJobNo.Value
End If
End Sub

tblRepairJob
JobNo - Autonumber - Primary Key
RepairCategoryID - Number
RepairTypeID - Number
+ others

qryRepairJob
JobNo
RepairCategoryID
RepairTypeID

'tblRepairCategory' links One to Many with 'tblRepairItem' (on
'RepairCategoryID')
'tblRepairType' links One to Many with 'tblRepairJob' (on 'RepairTypeID')

Two things - if I go to new record the form works fine, however when I click
back to an existing record I recieve error message :-

Syntax error (missing operator) in query 'JobNo ='

Obviously something amiss but I can't see it.
And secondly if I change the field item for 'cboRepairType' in a record, the
underlying query changes as does the table that supplies the query. However
when I reopen the form the amended record has reverted to the original, and
the underlying table and query values have reverted too - even after
saving??

Thanks in advance for any further assistance.
Sandy

 

[Arvin Meyer [MVP]]

The code sure looks OK to me as well. Since the .Value property is the
default property, true leaving it out. Also (it shouldn't be necessary but
humor the code anyway) adding:

& ";"

to the end. One of the things I've found when dealing with VBA code is that
the error is not always what it seems to be. Set a stop (breakpoint) and
step through the code until is errors, then check the variable values to see
if they're correct.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com