Solved

match text field to productid field value from other column

Posted on 2013-11-22
23
383 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now