M
Martin Ritchie
I'm creating an inventory database in Access 2000, and have my table and
forms already setup. I have created a form with the source being
"Department", with a sub form to show all the equipment list against that
department.
I want to create a drop down list, which shows all the departments, then
moves to the department chosen. I have done this on my other forms
successfully, where the form has been based on a table, and the ID field
(Primary key) has been used in the combo box to search for the record. ID
column is hidden in the combo box
The source of the combo box is the Inventory table itself. If I use only the
Department field, then I get the list of Departments sorted alphabetically.
If I use the ID field, and the Department field then the list shows every
instance of every department, rather than just listing each department once.
I tried searching on the Department field, but every time it returns an error.
Run-time error '3070'
The Microsoft Jet database engine does not recognise {deptartment} as a
valid field name or expression. - where {department} is the value returned
from the combo box selection.
Private Sub Combo9_AfterUpdate()
Dim rs As Object
Dim result As Variant
result = Me![Combo9]
Set rs = Me.Recordset.Clone
With rs
If Not IsNull(varid) Then
rs.FindFirst "Department = " & result
Me.Bookmark = .Bookmark
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acRefresh, ,
acMenuVer70
Else
DoCmd.GoToRecord acDataForm, "Form1", acNewRec
End If
End With
End Sub
Can anyone throw any light on where I'm going wrong?
forms already setup. I have created a form with the source being
"Department", with a sub form to show all the equipment list against that
department.
I want to create a drop down list, which shows all the departments, then
moves to the department chosen. I have done this on my other forms
successfully, where the form has been based on a table, and the ID field
(Primary key) has been used in the combo box to search for the record. ID
column is hidden in the combo box
The source of the combo box is the Inventory table itself. If I use only the
Department field, then I get the list of Departments sorted alphabetically.
If I use the ID field, and the Department field then the list shows every
instance of every department, rather than just listing each department once.
I tried searching on the Department field, but every time it returns an error.
Run-time error '3070'
The Microsoft Jet database engine does not recognise {deptartment} as a
valid field name or expression. - where {department} is the value returned
from the combo box selection.
Private Sub Combo9_AfterUpdate()
Dim rs As Object
Dim result As Variant
result = Me![Combo9]
Set rs = Me.Recordset.Clone
With rs
If Not IsNull(varid) Then
rs.FindFirst "Department = " & result
Me.Bookmark = .Bookmark
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acRefresh, ,
acMenuVer70
Else
DoCmd.GoToRecord acDataForm, "Form1", acNewRec
End If
End With
End Sub
Can anyone throw any light on where I'm going wrong?