Updating a Bookmark for a forum based on a table recordset

[forkEP]

I have a form "frmInvoice" which has two combo box's "cboDate" and "cboJobNumber". Based on the selection of these two pieces of data, I want the form to populate with the corresponding information. The Form is based on a query which has tables (tblInvoice, tblInvoiceNumber, tblTickets, tblJob, tblCustomer) in it. I have the code that does the check, but I am unsure as to how I will change the Bookmark. The InvoiceNumber is what ties everything of importance here together. Here is where I am:

' Find the record that matches the control.
Dim rst As DAO.Recordset
Dim Db As Database

Set Db = CurrentDb()
Set rst = Db.OpenRecordset("tblInvoiceNumber", dbOpenTable) 'set rs to the forms recordsource

'check that we have got some records
If Not (rst.BOF And rst.EOF) Then
'move to first record
rst.MoveFirst
'Loop Through all records
Do Until rst.EOF
If rst("JobNumber") = Me.cboJobNumber And rst("WeekEndingDateShown") = Me.cboDate Then
'we have found the invoice, now set the record to current
!!!!HERE IS MY PROBLEM!!!!
Exit Do
End If

rst.MoveNext
Loop
End If

'CLEAN UP After Yourself
'Close the Recordset
rst.Close
Set rts = Nothing

This works perfectly, but every different way I try to update the bookmark for the form will not work. Thank you very much for any assistance you can give me.

 

[forkEP]

oops, sorry for mistake, the title should be "Form" thanks again.

 

[Amiel]

Change the forms record source to a query string like this

Dim strSQl as string

strSQL = "SELECT tblInvoice.*, tblInvoiceNumber.*, tblTickets.*, tblJob.*, tblCustomer.*
FROM tblInvoice, tblInvoiceNumber, tblTickets, tblJob,
tblCustomer "

strSQL = strSQL & "WHERE tblCustomer.id = tblInvoiceNumber.customerid and tblInvoice.id = tblInvoiceNumber.InvoiceNumber and "

...
etc. more join statements for additional tables
...

"and tblInvoiceNumber.JobNumber=" & Me.cboJobNumber &
" and tblInvoiceNumber.WeekEndingDateShown = #" & Me.cboDate & "#"

then



me.RecordSource = strSQL

'me.requery and/or me.refresh updates the display.



The forms current record source (a query) can be used.
all is needed is to add the criterion:

" and tblInvoiceNumber.JobNumber=" & Me.cboJobNumber &
" and tblInvoiceNumber.WeekEndingDateShown = #" & Me.cboDate & "#".

using the form variables.

Use some debugging mechinisum like debug.print to view the query as you make your changes. The debugger opens with Ctl-G.

Set a breakpoint after the final assignment to strSQl and type ?strSQL in the immediate pane of the debugger. To see how things have gone.


Amiel
amielzz@mp4.it

http://amiel.dl.am

 

[forkEP]

Well, good news. After wrestling with it for about two hours, I got everything to work together. For anybody else, here is the code I used:

Dim strSQL As String

strSQL = "SELECT tblCustomer.*, tblJob.*, tblTickets.*, tblInvoiceNumber.*, tblInvoice.* FROM ((tblCustomer INNER JOIN tblJob ON [tblCustomer].[CustomerNumber]=[tblJob].[CustomerNumber]) INNER JOIN tblTickets ON [tblJob].[JobNumber]=[tblTickets].[JobNumber]) INNER JOIN (tblInvoiceNumber INNER JOIN tblInvoice ON [tblInvoiceNumber].[InvoiceNumber]=[tblInvoice].[InvoiceNumber]) ON [tblTickets].[TicketNumber]=[tblInvoice].[TicketNumber] "

strSQL = strSQL & "WHERE tblInvoiceNumber.JobNumber=" & Me!cboJobNumber & " and tblInvoiceNumber.WeekEndingDateShown=#" & Format(Me!cboDate, "mm-dd-yyyy") & "#"

Me.RecordSource = strSQL
Me.Requery

I ripped the "FROM" part straight out of the Me.RecordSource Hope this will help somebody else that might be having the same problem. Thank you very much Amiel, I was ready to throw my computer.