Combo Box Search 2nd SubForm

[spartansFC]

Hi

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:

cmbInvoiceSearch
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

Michael

 

[Andrzejek]

Try this:

    Dim rs As Object[blue]
Dim str as String
[/blue]
    Set rs = Me.Recordset.Clone[blue]
str = "Forms.[frmCustomers]![frmCustomersBookings Subform].[frmCustomerslInvoices Subform].Form![lngBookingID]" = " & Str(Nz(Me![cmbInvoiceSearch], 0))"  

Debug.Print str

rs.FindFirst str[/blue]

    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.

 

[spartansFC]

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))

 

[Andrzejek]

Interesting/unique way of naming fields in tables:
[tt]
[red]lng[/red]BookingID
[red]lng[/red]InvoiceID
[red]lng[/red]PeopleID
[red]str[/red]InvoiceNo
[/tt]



---- Andy

There is a great need for a sarcasm font.