Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Combo Box Search 2nd SubForm

Combo Box Search 2nd SubForm

Combo Box Search 2nd SubForm


The main set up of the database is:

MainForm: frmCustomers, tblCustomers, lngPeopleID (PK)
Subform1: frmCustomersBookings Subform, tblBookings, lngBookingID (PK), lngPeopleID(FK)
Subform2: frmCustomerslInvoices Subform, tblInvoices, lngInvoiceID (PK), lngBookingID (FK)

I've got 3 search combo boxes on the main form to search for either the customer name, booking number or invoice number, i'm stuck on the invoice number search.

I got the customer search to work via:
RowSource: SELECT tblCustomers.lngPeopleID, [strForename] & " " & [strSurname] AS staffname FROM tblCustomers ORDER BY [strForename] & " " & [strSurname];


rs.FindFirst "[lngPeopleID] = " & Str(Nz(Me![cmbCustomerSearch], 0)) 

I got the booking search to work via
Row Source: SELECT tblBookings.lngPeopleID, tblBookings.lngBookingID, tblBookings.strReferenceNumber FROM tblBookings ORDER BY tblBookings.strReferenceNumber;


rs.FindFirst "[lngPeopleID] = " & Str(Nz(Me![cmbBookingSearch], 0)) 

In the combo box i'm using to search for invoices, i have:

RowSource: SELECT tblInvoices.lngBookingID, tblInvoices.lngInvoiceID, tblInvoices.strInvoiceNo FROM tblInvoices ORDER BY tblInvoices.strInvoiceNo;
BoundColumn: 1
Coloumn Widths: 0;0;2.54

The AfterUpdate has


Private Sub cmbInvoiceSearch_AfterUpdate()

' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "Forms.[frmCustomers]![frmCustomersBookings Subform].[frmCustomerslInvoices Subform].Form![lngBookingID]" = " & Str(Nz(Me![cmbInvoiceSearch], 0))"  
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub 

i even tried


rs.FindFirst "Forms![frmCustomers]![frmCustomersBookings Subform].Form![frmCustomerslInvoices Subform].Form.[lngBookingID]" = " & Str(Nz(Me![cmbInvoiceSearch], 0))" 

Nothing happens when i select any invoice number from the combo box, i managed to get the other two working using the same method but i don't think i'm referencing the control of the subform properly.

Any ideas


RE: Combo Box Search 2nd SubForm

Try this:


Dim rs As Object
Dim str as String

    Set rs = Me.Recordset.Clone
str = "Forms.[frmCustomers]![frmCustomersBookings Subform].[frmCustomerslInvoices Subform].Form![lngBookingID]" = " & Str(Nz(Me![cmbInvoiceSearch], 0))"  

Debug.Print str

rs.FindFirst str

    If Not rs.EOF Then Me.Bookmark = rs.Bookmark 

and see what you get in Debug.Print

You may find out that instead of


str = "Forms.[frmCustomers]![frmCustomersBookings Subform].[frmCustomerslInvoices Subform].Form![lngBookingID]" = " & Str(Nz(Me![cmbInvoiceSearch], 0))" 
you need:


str = Forms.[frmCustomers]![frmCustomersBookings Subform].[frmCustomerslInvoices Subform].Form![lngBookingID] & " = " & Str(Nz(Me![cmbInvoiceSearch], 0)) 

---- Andy

There is a great need for a sarcasm font.

RE: Combo Box Search 2nd SubForm


Thanks for the help Andy, it was a quick fix in the end, don't know why i didn't think of it.

in the combo box i had


SELECT tblBookings.lngPeopleID, tblInvoices.lngBookingID, tblInvoices.lngInvoiceID, tblInvoices.strInvoiceNo FROM tblBookings INNER JOIN tblInvoices ON tblBookings.lngBookingID = tblInvoices.lngBookingID ORDER BY tblInvoices.strInvoiceNo; 

and in the after update event of the combo box i had


rs.FindFirst "[lngPeopleID] = " & Str(Nz(Me![cmbInvoiceSearch], 0)) 

RE: Combo Box Search 2nd SubForm

Interesting/unique way of naming fields in tables:



---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close