Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-17-2018, 05:17 AM   #1
Snowflake68
Newly Registered User
 
Join Date: May 2014
Location: Hampshire, England
Posts: 306
Thanks: 199
Thanked 4 Times in 4 Posts
Snowflake68 is on a distinguished road
Dlookup with Variables

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

Snowflake68 is offline   Reply With Quote
Old 04-17-2018, 05:24 AM   #2
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,448
Thanks: 71
Thanked 1,089 Times in 1,017 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Dlookup with Variables

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.
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
Snowflake68 (04-17-2018)
Old 04-17-2018, 06:15 AM   #3
Snowflake68
Newly Registered User
 
Join Date: May 2014
Location: Hampshire, England
Posts: 306
Thanks: 199
Thanked 4 Times in 4 Posts
Snowflake68 is on a distinguished road
Re: Dlookup with Variables

Quote:
Originally Posted by ridders View Post
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

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?

Snowflake68 is offline   Reply With Quote
Old 04-17-2018, 06:26 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,051
Thanks: 120
Thanked 1,384 Times in 1,356 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Dlookup with Variables

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 ?
__________________
If we have helped;
Please click the 'reputation' scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
Old 04-17-2018, 06:44 AM   #5
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,448
Thanks: 71
Thanked 1,089 Times in 1,017 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Dlookup with Variables

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 Only 'ONE t
Dim strBoxesPerPallet As Double Only 'ONE t

strBox = Me.cboBox
strPallet = Me.cboPallet
strBoxesPerPallet = DLookup(strPallet, "tbl_Q97i_PalletMatrix", "[StockCode] = '" & strBox & "'")
Suggest you read this link about using DLookup correctly:
https://support.office.com/en-us/art...b-bed10dca5937
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
Snowflake68 (04-17-2018)
Old 04-17-2018, 07:01 AM   #6
Snowflake68
Newly Registered User
 
Join Date: May 2014
Location: Hampshire, England
Posts: 306
Thanks: 199
Thanked 4 Times in 4 Posts
Snowflake68 is on a distinguished road
Re: Dlookup with Variables

Quote:
Originally Posted by Minty View Post
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.

Quote:
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"


Quote:
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 is offline   Reply With Quote
Old 04-17-2018, 07:07 AM   #7
Snowflake68
Newly Registered User
 
Join Date: May 2014
Location: Hampshire, England
Posts: 306
Thanks: 199
Thanked 4 Times in 4 Posts
Snowflake68 is on a distinguished road
Re: Dlookup with Variables

Quote:
Originally Posted by ridders View Post
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

Quote:
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 Only 'ONE t
Dim strBoxesPerPallet As Double Only 'ONE t

strBox = Me.cboBox
strPallet = Me.cboPallet
strBoxesPerPallet = DLookup(strPallet, "tbl_Q97i_PalletMatrix", "[StockCode] = '" & strBox & "'")
Quote:
Suggest you read this link about using DLookup correctly:
https://support.office.com/en-us/art...b-bed10dca5937
Thanks for the link but I already understand the syntax of standard DLookups already but just not how to use variables within them.

Snowflake68 is offline   Reply With Quote
Old 04-17-2018, 07:51 AM   #8
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,513
Thanks: 10
Thanked 1,239 Times in 1,179 Posts
MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light MarkK is a glorious beacon of light
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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
Snowflake68 (04-17-2018)
Old 04-17-2018, 07:58 AM   #9
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,051
Thanks: 120
Thanked 1,384 Times in 1,356 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Dlookup with Variables

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)
__________________
If we have helped;
Please click the 'reputation' scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Snowflake68 (04-17-2018)
Old 04-17-2018, 10:47 AM   #10
Snowflake68
Newly Registered User
 
Join Date: May 2014
Location: Hampshire, England
Posts: 306
Thanks: 199
Thanked 4 Times in 4 Posts
Snowflake68 is on a distinguished road
Re: Dlookup with Variables

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 is offline   Reply With Quote
Old 04-17-2018, 11:46 AM   #11
Snowflake68
Newly Registered User
 
Join Date: May 2014
Location: Hampshire, England
Posts: 306
Thanks: 199
Thanked 4 Times in 4 Posts
Snowflake68 is on a distinguished road
Re: Dlookup with Variables

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.
Snowflake68 is offline   Reply With Quote
Old 04-17-2018, 12:52 PM   #12
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,801
Thanks: 13
Thanked 1,293 Times in 1,232 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Dlookup with Variables

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

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Dlookup and variables Casper1973 Queries 3 08-08-2013 04:08 AM
Syntax help with two dlookup with two string variables latex88 Modules & VBA 7 04-25-2013 06:28 AM
Dlookup variables lcline General 3 06-24-2003 09:53 AM
Dlookup variables lcline General 1 02-01-2003 05:45 AM
Refering to variables with Dlookup Rich1968 Forms 1 01-24-2003 02:44 PM




All times are GMT -8. The time now is 05:51 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World