subform to report not working all the time

[reidgivens]

I have a report that should take the primary keys defined in a form and generate the report with that information. It works with the first two test records in the database, but anything after that doesn't work.

I have the report opened by a button on the form with the proper information, but when the report opens it is empty.

The open report code is:

----------------------------code start

Private Sub open_invoice_Click()
On Error GoTo Err_open_invoice_Click

Dim stDocName As String
Dim numvar As String

numvar = "c_id_num=" & Forms!customers!c_id_num & "AND i_id_num=" & Forms!customers!vehicles!invoice!i_id_num_box & "AND v_id_num=" & Forms!customers!vehicles!v_id_num
stDocName = "customers"
DoCmd.OpenReport stDocName, acPreview, , numvar

Exit_open_invoice_Click:
Exit Sub

Err_open_invoice_Click:
MsgBox Err.Description
Resume Exit_open_invoice_Click

End Sub

--------------------------- code stop

Please help thanks
Reid Givens


Reid Givens
Webdeveloper

http://www.reidgivens.com

 

[CosmoKramer]

Reid,

Are you sure that the combination of the three id_num fields exists for these "missing" records??

Do any of these id_num fields hold any alphanumeric data?? Currently you will find numeric values only.....

 

[dhookom]

You should place a Break Point in your code so that you can open the immediate window (Ctrl+G) and enter:
? numvar
I think you are having problems with:
Forms!customers!vehicles!invoice!i_id_num_box
If this control is on the form that is running the code then all you should need is
Me.i_id_num_box
I assume all of your fields are numeric.

Duane
MS Access MVP

 

[reidgivens]

I appreciate the help, but shortly after posting this question I discovered the problem. I don't however, have the solution for it.

My database has a customers table, a vehicle table, and an invoice table. The cutomer table and vehicle table are linked by the auto number primary key of the customer table(c_id_num), and the vehicle and invoice table are linked by the auto number primary key of the vehicle table (v_id_num). The problem is, when inputting data in the form, the invoice table can't make a link to the customer table. it is supposed to link via the c_id_num, but it doesn't populate that feild in the invoice table. It will make the link to the vehicle, but not the customer.

I am at a loss. I'm not sure I am explaining this correctly, but any help would be much appreciated.

Reid Givens
Webdeveloper

http://www.reidgivens.com

 

[dhookom]

Doesn't your Invoice table link to the Vehicle table which should then link to the Customer table? A link directly from the Customer to the Invoice should not be necessary.

Duane
MS Access MVP