Solved

match text field to productid field value from other column

Posted on 2013-11-22
23
393 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

920 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

16 Experts available now in Live!

Get 1:1 Help Now