match text field to productid field value from other column

Hi all,

I developed a barcode to scan with a barcode scanner for scanning article numbers.

Some articles allready have a barcode. So I thought: If we scan sometimes the new barcode for the article number and some times the barcode allready on the product and read them from the scanner in a new field. I maybe can match the value of scan to the combo field on the same form. my article numbers are always like "10000-001" and barcodes a 8 value or 13 value.

So when I scan and they match one of the fields, I can read them in one new field but when it is aan article number it has to match this number to the article number field (dropdown productid article number) and if it is an ean barcode number it has to match the (dropdown productid ean)

Can you help me out please?

Private Sub Scan_AfterUpdate()

If Me![Scan] Like "*-*" Then Me![ProductID].Column(2) = Me.Scan Else
Me![ProductID].Column(3) = Me.Scan

End Sub

Open in new window

mvdwalAsked:
Who is Participating?
 
AccessGuy1763Connect With a Mentor Commented:
You're right!!  My bad.

Ok two different ways you could go about this issue:

1) Enclose the DLookUp with the NZ() function something like this:

lngProductID = NZ(DLookUp("ProductID","Products","EAN13NL='" & Me.Scan & "'"),-1)

Open in new window


Effectively this means that DLookUp() will return -1 if nothing is found, averting the error.  I'm assuming -1 isn't a valid ProductID.

2) I think it might work as is if you just change "lngProductID" to a type Variant instead of Long:

Dim varProductID as Variant

Open in new window

0
 
AccessGuy1763Commented:
It's a little hard to dissect exactly what you're trying to do, but based on your code sample it seems that you want to select the value in your "ProductID" combobox based on either column 2 or column 3 (depending on what is scanned?).

If I'm right, then the problem with your code is that you're trying to set a value to a combobox column when they are read only.  You can only set the value of the bound column (which I'm going to assume is ProductID in this case).

[Modified by SouthMod to remove links]
0
 
mvdwalAuthor Commented:
Hi I try to explain.

I have a form with three fields:

fieldone is a text field (for the scanner)
fieldtwo is a dropdown
fieldthree is a dropdown

if I type in fieldone the number and its an article number (10000-000) than in fieldtwo it has to match or lookup that number

if I type in fieldone the number and its an barcode (8 digits or 13 digit number) than in fieldthree it has to match or lookup that number
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
AccessGuy1763Commented:
Ok that's not quite what I was picturing when I looked at your code.

I still feel like I'm short a bit of information because I don't know where field two and field three are looking up their information from.  However, assuming it's a table, the DLookUp() route would probably be the easiest way to go:

Private Sub Scan_AfterUpdate()

   If Me![Scan] Like "*-*" Then
 
     Me!fieldtwofieldname = Dlookup("FieldYouAreLookingUp","tblArticlesAndStuff","ArticleNumber=" & me.Scan)

   Else

      Me!fieldthreefieldname = Dlookup("FieldYouAreLookingUp","tblBarcodesAndStuff","BarCode=" & me.Scan)

   End If

End Sub

Open in new window


Again, this is my best guess based on the information I have so far.  Obviously the field you are TRULY looking up is not going to be called "FieldYourAreLookingUp" and I doubt the relevant tables are named "tblArticlesAndStuff" or "tblBarcodesAndStuff", so you'll need to modify some of the above to match your actual field, table, and column names.

Dlookup()
0
 
mvdwalAuthor Commented:
Hi sorry it is not working. Thanks for helping me out!

I have a form named Offer Barcode Subform with three fields:
The fields in the form are:
Scan is a text field (for the scanner)
art.nr is a dropdown (ProductID) these are linked fields from the Products table and show SerialNumber
ProductEAN is a dropdown (ProductID)  these are linked fields from the Products table and show EAN13NL

the data on the form is stored in the table Offer Details
0
 
AccessGuy1763Commented:
What is the Control Source (destination field) for the "art.nr" dropdown and the "ProductEAN" dropdown?
0
 
mvdwalAuthor Commented:
It's ProductID
control-source.JPG
query.JPG
0
 
AccessGuy1763Commented:
I think this might be where the confusion lies.  There's no good reason I can think of to have multiple controls with the same control source.  You believe this is necessary in order to select ProductID based on what gets scanned or entered into your "Scan" field, but you should just adjust your logic to do a different DLookUp in those two different scenarios.  Both Dlookups would return a ProductID, and you would then set your ONE ProductID field to that number.

You also might believe you need multiple fields bound to ProductID because you want to display the SerialNumber and the Bar Code (EAN13NL) at the same time, but there's better ways to handle simple display of data related to the ProductID.

This is how I believe your code should be structured:

Private Sub Scan_AfterUpdate()

   If Me![Scan] Like "*-*" Then
 
     Me!art.nr = Dlookup("ProductID","Products","SerialNumber=" & me.Scan)

   Else

      Me!art.nr = Dlookup("ProductID","Products","EAN13NL=" & me.Scan)

   End If

End Sub

Open in new window

0
 
mvdwalAuthor Commented:
Thanks a lot but it 's not working
Can not find the
null.JPG
0
 
AccessGuy1763Commented:
Obviously the German makes it quite tough for me to understand... but I think your DLookUp is returning nothing.

You can test your DLookUps by typing something like this in the Immediate Window:

?DLookUp("ProductID","Products","SerialNumber=123SampleNum123")

Then just hit enter at the end of the line.

This also bring up the point that your code will fail if the DLookUp returns no value.  This is the change I would make to handle that:

Private Sub Scan_AfterUpdate()

   Dim lngProductID as long

   If Me![Scan] Like "*-*" Then
 
     lngProductID = Dlookup("ProductID","Products","SerialNumber=" & me.Scan)

     if isnull(lngProductID)=false then
  
         Me!art.nr = lngProductID 

      end if

   Else

      lngProductID = Dlookup("ProductID","Products","EAN13NL=" & me.Scan)      

      if isnull(lngProductID)=False then

         Me!art.nr = lngProductID

      end if

   End If

End Sub

Open in new window


So, make the changes above to handle the scenario where the DLookUp() returns nothing.  When you run it, you should get no error message, but the "art.nr" dropdown will probably not get selected because I suspect the DLookUp()s aren't quite right.  Based on your first description, I think you actually only want to search the Products table based on part of the scanned in number, not the whole thing (which is the way I have it set up).  So, of the following DLookUps:

Dlookup("ProductID","Products","SerialNumber=" & me.Scan)
Dlookup("ProductID","Products","EAN13NL=" & me.Scan)

The portions that likely need to be altered are just these parts:

"SerialNumber=" & me.Scan
"EAN13NL=" & me.Scan

Again, I recommend using the Immediate Window to test your Dlookups() and make sure they are returning the ProductID you expect.  Another potential problem is that the fields in question might be text, which would mean you need to enclose the criteria with single quotes like this:

"SerialNumber='" & me.Scan & "'"
"EAN13NL='" & me.Scan & "'"
0
 
mvdwalAuthor Commented:
Well thanks a lot! I will try this weekend your solutions.
0
 
AccessGuy1763Commented:
Good Luck! :)
0
 
mvdwalAuthor Commented:
Thanks! Keep you posted!
0
 
mvdwalAuthor Commented:
[Scan].Requery
   If Me![Scan] Like "*-*" Then
Me![ID]= DLookup("[ProductID]", "Products", "[SerialNumber] ='" & Me![Scan] & "'")
  Else
  Me![ID]= DLookup("[ProductID]", "Products", "[EAN13NL] ='" & Me![Scan] & "'")
   End If

Open in new window


This did the trick!

Thanks a lot AccessGuy1763
0
 
mvdwalAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for mvdwal's comment #a39671252
Assisted answer: 500 points for AccessGuy1763's comment #a39669303

for the following reason:

I tried multiple DLookup solutions
0
 
mvdwalAuthor Commented:
If the scanned data is not recognized in ProductID I will get a failure What can change to this code if the data is not recognized.

[Scan].Requery
   If Me![Scan] Like "*-*" Then
Me![ID]= DLookup("[ProductID]", "Products", "[SerialNumber] ='" & Me![Scan] & "'")
  Else
  Me![ID]= DLookup("[ProductID]", "Products", "[EAN13NL] ='" & Me![Scan] & "'")
   End If 

Open in new window

0
 
AccessGuy1763Commented:
I have an above comment that actually addresses what to do about the scenario where your Dlookup() doesn't return anything:

Private Sub Scan_AfterUpdate()

   Dim lngProductID as long

   If Me![Scan] Like "*-*" Then
 
     lngProductID = Dlookup("ProductID","Products","SerialNumber='" & me.Scan & "'")

     if isnull(lngProductID)=false then
  
         Me!ID = lngProductID 

      end if

   Else

      lngProductID = Dlookup("ProductID","Products","EAN13NL='" & me.Scan & "'")      

      if isnull(lngProductID)=False then

         Me![ID] = lngProductID

      end if

   End If

End Sub

Open in new window

0
 
mvdwalAuthor Commented:
Hi Accesguy,

Thanks for your help again.

I'll get an error: invalid use of null

invalid use of null
0
 
mvdwalAuthor Commented:
Your my hero!!!

Can you help me also with a counterfield?
I've programmed a counterfield in the form.  I want to open the form and filter to the maximum of 25 records  (a filter from 1 to 25) do you know how to place the filter?
0
 
AccessGuy1763Commented:
To filter a form, the easiest way is to just use the Filter property:

Me.Filter = "ProductID=" & lngSomeProductID
Me.FilterOn = True

Open in new window


To apply this when opening the form, use the Form_Load event:

Private Sub Form_Load()

   Me.Filter = "ProductID=" & lngSomeProductID 
   Me.FilterOn = True

End Sub

Open in new window


The only problem now is that I don't know exactly what you mean by "maximum of 25 records" so I can only guess at how to determine what lngSomeProductID should be.

So, for this example let's say we want the max ProductID:

lngProductID = DMax("ProductID","Products")

Open in new window


So here's the whole thing put together:

Private Sub Form_Load()

   Dim lngProductID as long

   lngProductID = DMax("ProductID","Products")

   Me.Filter = "ProductID=" & lngProductID 
   Me.FilterOn = True

End Sub

Open in new window

0
 
mvdwalAuthor Commented:
Dear AccesGuy,

Thanks but I do not understand. Where do I need to specify the 25 records? And I have to specify the next 25 records for other report and so on. The report has pictures and after 30 records there are sometimes empty pictures
0
 
AccessGuy1763Commented:
The main problem is that I don't quite understand which 25 records you want.  It sounded to me like you wanted to filter to one of 25 records on your Form.  Is this something you're trying to do with a Report?
0
 
mvdwalAuthor Commented:
Sorry, I want 25 records of the subform in the first report. I've programmed a field in the subform called counter. This field is in the table offer detail.

In the other reports I want the rest of the records from the subform. Is this easy to do? I think it is easy. But I don't know how.

The records I want to show are in a subreport by the way.

Here's the topic so you can earn the points

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28302363.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.