Dlookup with Variables (1 Viewer)

Snowflake68

Registered User.
Local time
Today, 11:09
Joined
May 28, 2014
Messages
452
I have two combo boxes (both Text) which I need to use in order to lookup a numeric value in a table matrix.

cboBox and cboPallet are my two combos

I have the following code but I am getting an error "Invalid use of Null"

Code:
Dim strBox As String
Dim strPallet As String
Dim strBoxesPerPallet As Double

strBox = cboBox ' this is the rowsource in the table to lookup
strPallet = cboPallett ' this determines which field in the table to lookup

'This is the line which causes the error
strBoxesPerPallet = DLookup("'" & strPallett & "'", "tbl_Q97i_PalletMatrix", "StockCode = " & "'" & strBox & "'")

MsgBox strBoxesPerPallet

But I replace the strPallett with one of the actual field names then it works OK so I know the value exists and the "Invalid use of Null" error is a red herring. The strBox variable is working though, its just the strPallett variable that I cannot get the syntax correct.

This code works
Code:
strBoxesPerPallet = DLookup("[PALLET-UK]", "tbl_Q97i_PalletMatrix", "StockCode = " & "'" & strBox & "'")

Any ideas please, Im pulling my hair out on this one.

Thanks
 

isladogs

MVP / VIP
Local time
Today, 11:09
Joined
Jan 14, 2017
Messages
18,186
Three errors with the first part of the dlookup.
1. Spelling of strPallet not consistent.
2. The first part of your dlookup shouldn't use delimiters as you have done
3. The syntax for dlookup is
Code:
=DLookup(fieldname, tablename, filter criteria)
The first two arguments need to be enclosed in "". The criteria uses delimiters.
 

Snowflake68

Registered User.
Local time
Today, 11:09
Joined
May 28, 2014
Messages
452
Three errors with the first part of the dlookup.
1. Spelling of strPallet not consistent.
2. The first part of your dlookup shouldn't use delimiters as you have done
3. The syntax for dlookup is
Code:
=DLookup(fieldname, tablename, filter criteria)
The first two arguments need to be enclosed in "". The criteria uses delimiters.


School boy error for the typo :banghead:

However I have changed my code to that of the below but is giving the wrong results and just pulls back the name of the value for strPallet

Code:
Dim strBox As String
Dim strPallett As String
Dim strBoxesPerPallett As Double

strBox = cboBox
strPallet = cboPallet

strBoxesPerPallet = DLookup("'" & strPallet & "'", "tbl_Q97i_PalletMatrix", "StockCode = " & "'" & strBox & "'")

so I removed the quotes around the variable for strPallet but that but that gives a syntax error 3075 missing operator in query expression.

Code:
strBoxesPerPallet = DLookup(strPallet, "tbl_Q97i_PalletMatrix", "StockCode = " & "'" & strBox & "'")

any other ideas please?
 

Minty

AWF VIP
Local time
Today, 11:09
Joined
Jul 26, 2013
Messages
10,355
Your data isn't really stored properly I'm afraid.
A field name should never be the data you are trying to store.

That said this should work.

Code:
Dim strBox As String
Dim strPallett As String
Dim strBoxesPerPallett As Double

strBox = Me.cboBox
strPallet = Me.cboPallet
strBoxesPerPallet = DLookup(strPallet, "tbl_Q97i_PalletMatrix", "[StockCode] = '" & strBox & "'")

Are the comboboxes bound columns the actual text or an ID field ?
 

isladogs

MVP / VIP
Local time
Today, 11:09
Joined
Jan 14, 2017
Messages
18,186
You only dealt with the first of the 3 errors I listed

The code below will only work if Me.cboPallet contains FIELD NAMES as the first part of the DLookup has to be a field name as already stated

Code:
Dim strBox As String
Dim strPallet As String [COLOR="SeaGreen"]Only 'ONE t[/COLOR]
Dim strBoxesPerPallet As Double [COLOR="SeaGreen"]Only 'ONE t[/COLOR]

strBox = Me.cboBox
[B]strPallet = Me.cboPallet[/B]
strBoxesPerPallet = DLookup([B]strPallet[/B], "tbl_Q97i_PalletMatrix", "[StockCode] = '" & strBox & "'")

Suggest you read this link about using DLookup correctly:
https://support.office.com/en-us/article/dlookup-function-8896cb03-e31f-45d1-86db-bed10dca5937
 

Snowflake68

Registered User.
Local time
Today, 11:09
Joined
May 28, 2014
Messages
452
Your data isn't really stored properly I'm afraid.
A field name should never be the data you are trying to store.

The field names in the Pallet Matrix have to be the same as the values selected in the cboPallet otherwise it wont know which field to lookup. I want the value in the cboPallet to determine which field to lookup in the matrix table.

That said this should work.

Code:
Dim strBox As String
Dim strPallet As String
Dim strBoxesPerPallet As Double

strBox = Me.cboBox
strPallet = Me.cboPallet
strBoxesPerPallet = DLookup(strPallet, "tbl_Q97i_PalletMatrix", "[StockCode] = '" & strBox & "'")

This still doesnt work and produces error 2428 "You have entered an invalid argument in a domain aggregate function"


Are the comboboxes bound columns the actual text or an ID field ?
The combo boxes bound the actual text value, the values are generated from a query in an SQL linked table.
 

Snowflake68

Registered User.
Local time
Today, 11:09
Joined
May 28, 2014
Messages
452
You only dealt with the first of the 3 errors I listed
I can only see 3 points listed and thought I dealt with them. I did correct all of the typos but when I pasted it back into here I pasted the wrong bit. Not having a good day :(

The code below will only work if Me.cboPallet contains FIELD NAMES as the first part of the DLookup has to be a field name as already stated
So are you saying that I cannot determine the field name with a variable from a combo box?

Code:
Dim strBox As String
Dim strPallet As String [COLOR="SeaGreen"]Only 'ONE t[/COLOR]
Dim strBoxesPerPallet As Double [COLOR="SeaGreen"]Only 'ONE t[/COLOR]

strBox = Me.cboBox
[B]strPallet = Me.cboPallet[/B]
strBoxesPerPallet = DLookup([B]strPallet[/B], "tbl_Q97i_PalletMatrix", "[StockCode] = '" & strBox & "'")
Thanks for the link but I already understand the syntax of standard DLookups already but just not how to use variables within them.
 

MarkK

bit cruncher
Local time
Today, 04:09
Joined
Mar 17, 2004
Messages
8,178
Its a strong indication that your data is not normalized if you need to change the field name in your DLookup(). A row should never contain multiple 'sibling' values from which you then might need to select one, as you are doing. If you are doing that, then your one row has many values, and you have collapsed your one-to-many relationship, which should correctly be modeled using two tables. It is impractical in a database system, as you are finding, to model a one-to-many relationship using one row and many fields in the same row.

One row should hold, at most, the definition of one thing, one object, one data point. If you are cherry picking fields in that row because you have multiple values describing the same data dimension, you have made an error.

Also, if you need a matrix, you should create one in code as a multi-dimensional array, and then load that structure with data in advance of your operation. Then you can very rapidly address that structure using subscripts, which is what it looks like you are trying to do.
hth
Mark
 

Minty

AWF VIP
Local time
Today, 11:09
Joined
Jul 26, 2013
Messages
10,355
You can determine the field name and use it in a variable, I did test it.
But as Markk and I pointed out you shouldn't store the pallet name as a field name. If you need to add a pallet you have to change the structure of your table. This is not a good design.

What you should have is table with
BoxNr, PalletType, NoOFBoxes

Then you simply look up the two criteria, or better still add them to your query results / combo listings etc. etc.

You've stored the Data like a spreadsheet - Wide
Generally data in databases is long and thin :) (records going down)
 

Snowflake68

Registered User.
Local time
Today, 11:09
Joined
May 28, 2014
Messages
452
I have found another way as I couldn't get the field name to work using a variable. I am just going to reference the actual field name in the Dlookup and use if statements so that each Pallet has a separate lookup depending on what value they select from the combo box. Maybe not the way you guys would do it but it works for me.

The only reason I have the matrix table like this is because the end user needs to be able to add more stock codes (boxes) and for ease of use a matrix like this is easier for the user to manage. They will NEVER need to add different Pallets so that's not an issue.

I will admit that I'm just not that clever with the relationships side of databases nor when it comes to structuring tables. I am self taught and this sort of thing isnt 100% of my job so I have to make do with the skills I have. I very much appreciate the help and advice you guys all give me. So thanks again
 

Snowflake68

Registered User.
Local time
Today, 11:09
Joined
May 28, 2014
Messages
452
Right so I have taken the advice on here and have now restructured my table to have 3 fields. StockCode, Pallet, and BoxesPerPallet and then amended my lookup to lookup both the stock code and pallet from the two combo boxes and all works perfectly. I will just need to sort out how the user manages the table to add boxes in the future. I will probably just create a form for them to add new ones and have a crosstab query as a report to display the matrix just a a visual representation..
Code:
strBoxesPerPallet = DLookup("[BoxesPerPallet]", "tbl_Q97i_Pallets_BoxesPerPallet", "[StockCode]='" & cboBox & "' AND [Pallet]='" & cboPallet & "'")

Thanks again.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:09
Joined
Feb 19, 2002
Messages
42,981
When referencing form controls, use the Me. prefix. This is more efficient for the compiler and it also gives you intellisense as you type.

Me.cboBox
 

Users who are viewing this thread

Top Bottom