Lookup in Form and Report filtering

[agure]

i've been having an issue with for a week now and no one can seem to resolve.

There is a data type mismatch for some reason when I'm searching for a textvalue thats a lookup from another table. Access saves the lookup in the table as a number value and then it shows up as the proper text value in a report. This makes it so I can never search for it can anyone help me out.

DoCmd.OpenReport "Income Report", acViewPreview, , "[Truck#]='" & Me.Truckcombobox & "' And [Invoice#]='" & Me.invoicetextbox & "'"

This code is rendered useless because of the data type mismatch but has worked fine for me before



EDIT I had a different question up but I realize if i can't resolve this issue it'll lead to all these other issues.

[ItsMe]

What is the bound column for your combobox? The syntax you are using requires the bound column to be text data type. Post the RowSource SQL for your combo.

[agure]

What is the bound column for your combobox? The syntax you are using requires the bound column to be text data type. Post the RowSource SQL for your combo.

Row Source for the ComboBOX: SELECT [Trucks in Fleet].[Truck#] FROM [Trucks in Fleet] ORDER BY [Truck#];

I did a test with truck# by itself and it seems to work fine.

I did a few tests with invoice# and i run into some issues.

Table1: New Job
Table2: Income

Invoice# originally comes from a table called New Job. Then I created a lookup in a new table called Income for Invoice#.

Now if I create for example a combobox with Invoice# from table Income, the values I get are 1,2,3,4,5 etc etc I get just number values of the ID not sure why I even played around with the relationship.

In short: I believe it saves the ID value for the lookup instead of the text value both would return the same row of results in a normal report.


I hope that makes sense.

[June7]

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be InvoiceNum or Invoice_Num.

Which field is the primary key? Primary key value should be saved as foreign key in related table.

[agure]

The primary key is the ID#, I will change the name of the table. So what should I do here in regards to this primary key and foreign key can you further explain.

I have Invoice# and Truck# in the Income Table which are both lookup values from different tables.

Should I make the primary keys Truck# in the other table and Invoice# in the other tables??

[ItsMe]

....In short: I believe it saves the ID value for the lookup instead of the text value both would return the same row of results in a normal report.

...

It is not advisable to use lookup fields. Yes, the lookup field is storing a number (index) not the literal text you view.

[June7]

Whether you use autonumber ID or TruckNum (or InvoiceNum) as primary key is your choice. There are arguments both ways. Using the ID is supposed to be faster indexing but using the others would not require table joins in query to retrieve TruckNum and InvoiceNum.

Are you also changing the field names?

[agure]

Yes i will change all my field names now.

So what approach must I do because I would like to build a report for Income where It will just show me the values that I put in the search form for Truck# and Invoice#. I would like the values to be selected from comboboxes.

[June7]

Controls used only for the input of filter criteria should be UNBOUND.

Options for dynamic filtered report:

1. parameterized query, review http://www.datapigtechnologies.com/f...mtoreport.html

2. VBA code that constructs filter criteria string and passes it to the report, like:
DoCmd.OpenReport "report name", , , strFilter

The filter criteria must agree with the field that is being searched on. You can use comboboxes but make sure you are pulling the correct value from combobox for the search parameter.

I agree with ItsMe - do not build lookups in table, especially if they involve alias. http://access.mvps.org/access/lookupfields.htm

[agure]

Controls used only for the input of filter criteria should be UNBOUND.

Options for dynamic filtered report:

1. parameterized query, review http://www.datapigtechnologies.com/f...mtoreport.html

2. VBA code that constructs filter criteria string and passes it to the report, like:
DoCmd.OpenReport "report name", , , strFilter

The filter criteria must agree with the field that is being searched on. You can use comboboxes but make sure you are pulling the correct value from combobox for the search parameter.

I agree with ItsMe - do not build lookups in table, especially if they involve alias. http://access.mvps.org/access/lookupfields.htm

I will go through these things..

May I ask so instead of creating a lookup in a table how should i approach lookups exactly?

[June7]

Build comboboxes on form.

[agure]

So I should just remove all relationships in my project and start created combo boxes in forms?

[agure]

Build comboboxes on form.

This just changed my life. lol thanks haha

[agure]

Now it seems this works now here is another question in regards to comboboxes.

SO I have Invoice# and Truck#

What I would like to do is limit the amounts of trucks being displayed on the combobox menu depending on the Invoice#.

FOR Example:

Invoice# 12345 has only ACV1234, ACT1234 and ACG1234.

How can I make it so only those 3 Truck#'s show up in the truck combobox menu when i select Invoice# 12345 ? I'm not too sure how to filter this exactly. Thanks guys!!

[ItsMe]

You need to determine the criteria that will distinguish what trucks are available for a given invoice.


The query builder may help you to build the SQL for the combobox's RowSource. In Design View of your form, select the combo and go to the property sheet. Select the Event tab and click the ellipses(...)

.