Solved

match text field to productid field value from other column

Posted on 2013-11-22
23
403 Views
Last Modified: 2013-11-25
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

0
Comment
Question by:mvdwal
  • 13
  • 10
23 Comments
 
LVL 4

Expert Comment

by:AccessGuy1763
ID: 39668729
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
 

Author Comment

by:mvdwal
ID: 39668771
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
 
LVL 4

Expert Comment

by:AccessGuy1763
ID: 39668845
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:mvdwal
ID: 39669162
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
 
LVL 4

Expert Comment

by:AccessGuy1763
ID: 39669186
What is the Control Source (destination field) for the "art.nr" dropdown and the "ProductEAN" dropdown?
0
 

Author Comment

by:mvdwal
ID: 39669206
It's ProductID
control-source.JPG
query.JPG
0
 
LVL 4

Expert Comment

by:AccessGuy1763
ID: 39669303
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
 

Author Comment

by:mvdwal
ID: 39669463
Thanks a lot but it 's not working
Can not find the
null.JPG
0
 
LVL 4

Expert Comment

by:AccessGuy1763
ID: 39669538
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
 

Author Comment

by:mvdwal
ID: 39669565
Well thanks a lot! I will try this weekend your solutions.
0
 
LVL 4

Expert Comment

by:AccessGuy1763
ID: 39669580
Good Luck! :)
0
 

Author Comment

by:mvdwal
ID: 39670003
Thanks! Keep you posted!
0
 

Author Comment

by:mvdwal
ID: 39671252
[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
 

Author Comment

by:mvdwal
ID: 39674979
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
 

Author Comment

by:mvdwal
ID: 39674357
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
 
LVL 4

Expert Comment

by:AccessGuy1763
ID: 39674691
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
 

Author Comment

by:mvdwal
ID: 39674734
Hi Accesguy,

Thanks for your help again.

I'll get an error: invalid use of null

invalid use of null
0
 
LVL 4

Accepted Solution

by:
AccessGuy1763 earned 500 total points
ID: 39674783
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
 

Author Comment

by:mvdwal
ID: 39674978
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
 
LVL 4

Expert Comment

by:AccessGuy1763
ID: 39675013
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
 

Author Comment

by:mvdwal
ID: 39675124
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
 
LVL 4

Expert Comment

by:AccessGuy1763
ID: 39675143
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
 

Author Comment

by:mvdwal
ID: 39675347
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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question