Speed Print Key

[yeasir01]

Hello,

I have a form I designed to be able to input data & print shelf tags quickly. An idea I had was add a quick-print toggle switch when switch on any barcode that's keyed in or scanned using a wedge scanner will change the [select] for that item from false to true.

I tried the following with no luck. Any help would be appreciated.

Dim strSql As String
    strSql = "Update MerchandiseTable Set [Select] = true" & "where upc = me.searchtext"
    CurrentDb.Execute strSql

 

[June7]

Concatenation is incorrect.

strSql = "Update MerchandiseTable Set [Select] = true where upc = " & Me.searchtext

SELECT is a reserved word. Should not use reserved words as names for anything.

 

[yeasir01]

That didn't seem to work i recieved a run time error 3464.Good point on the reserved word, ill have to go back and change that to selected instead. Any other thoughts?

 

[arnelgp]

strSql = "Update MerchandiseTable Set [Select] = true where upc = " & Chr(34) & me.searchtext & Chr(34)

 

[isladogs]

Just wondering. How is clicking a toggle button any faster than clicking a checkbox?

 

[June7]

Yes, if upc is a text type field, use arnel's syntax or

strSql = "Update MerchandiseTable Set [Select] = true where upc = '" & me.searchtext & "'"

 

[yeasir01]

Just wondering. How is clicking a toggle button any faster than clicking a checkbox?

toggle method
scan, scan, scan to add three items to print list then print

Manual Tick Option
scan to search
tick checkbox
scan to seatch
tick checkbox

 

[yeasir01]

strSql = "Update MerchandiseTable Set [Select] = true where upc = " & Chr(34) & me.searchtext & Chr(34)

Thank you arnelgp! This worked like a charm.

 

[yeasir01]

I had to redesign the database to work for multi user. The flaw in my first design was the check box was bound & stored on table which was used for selecting items to print. I quikly relized that will not work when the tables are being shared. @CJ_london made a great suggestion https://www.access-programmers.co.uk/forums/showthread.php?t=289116 , to basically highlight a row & store the ID on an unbound text box on the main form for query actions & what not. Now my speed print button no longer works

Previous Code

Dim strSql As String
strSql = "Update MerchandiseTable Set [selected] = true where upc = " & Chr(34) & Me.SearchText & Chr(34)
CurrentDb.Execute strSql

CJ_Londin code for selecting & deselecting items

If InStr("|" & Forms("MainSearchForm").SelectedData, "|" & ID & "|") Then
       Forms("MainSearchForm").SelectedData = Replace(Forms("MainSearchForm").SelectedData, ID & "|", "")
    Else
       Forms("MainSearchForm").SelectedData = Forms("MainSearchForm").SelectedData & ID & "|"
    End If

Im having a hard time merging the two codes to make it work. What I need is a UPC lookup if there is a match then mark that record as selected.

 

[CJ_London]

don't see why you need that code. If you have done what I suggested in the other thread, you would just need to run the report. The code you are showing is updating a table (which is what you wanted to stop doing due to being multiuser) I presume the next bit of your code (not shown) is to run the report - that's all you need

 

[yeasir01]

don't see why you need that code. If you have done what I suggested in the other thread, you would just need to run the report. The code you are showing is updating a table (which is what you wanted to stop doing due to being multiuser) I presume the next bit of your code (not shown) is to run the report - that's all you need

I actually added the speed key so I can scan a barcode and have that item put in a que for printing or displaying the report. Hope that makes sense. It speeds up the process of select only the items you would like to print.

 

[CJ_London]

not sure which event you are using when you scan a barcode but sounds like you are only adding to the list, not deselecting so just use that part of the code. You don't need the sql

Forms("MainSearchForm").SelectedData = Forms("MainSearchForm").SelectedData & ID & "|"

leave the other code as it is so you can deselect it if required

 

[yeasir01]

not sure which event you are using when you scan a barcode but sounds like you are only adding to the list, not deselecting so just use that part of the code. You don't need the sql

Forms("MainSearchForm").SelectedData = Forms("MainSearchForm").SelectedData & ID & "|"

leave the other code as it is so you can deselect it if required

I attached a picture to hopfully convey the message a little better.

 

[yeasir01]

Heres what I came up with, Is this the best way to do this?

Forms("MainSearchForm").SelectedData = dlookup("id","merchandisetable", searchtext) & "|"

 

[CJ_London]

sorry, no idea and I don't see the relevance of the image you provided

 

[yeasir01]

For anyone doing the a similar project here's the syntax that worked for me. The one posted before this one did not work.

if Me.SpeedScan = True And DCount("UPC", "merchandisetable", "UPC=" & "Forms!MainSearchForm!searchtext") = 1 Then
Me.SelectedData = Me.SelectedData & DLookup("ID", "merchandisetable", "UPC=" & "Forms!MainSearchForm!searchtext") & "|"

Im not sure if it is the most effiecient, but it certainly works.

 

[arnelgp]

you can further reduced your code:

if Me.SpeedScan = True Then _
Me.SelectedData = Me.SelectedData & (DLookup("ID", "merchandisetable", "UPC=" & "Forms!MainSearchForm!searchtext") + "|")