VBA code filtering issue when attached to Text Box (1 Viewer)

Lateral

Registered User.
Local time
Today, 14:48
Joined
Aug 28, 2013
Messages
388
Hi Everyone,

I have been trying to resolve this issue for hours and have finally decided to ask for some help as I am
sure it is something very simple I'm missing.

I have provided a "cut down" sample database (XXLateral.zip) containing just the form and 2 tables and a query. It is in Access 2007 format.

An overview:

I have developed a database that contains stock and inventory functionality.

"Parts" are attached to "Locations".

Both Parts and Locations have barcodes that can be printed off and attached to the individual part and the actual storage box (Location) that the part is located in.

Currently, when a stocktake is actioned, a report is generated, sorted in Location order and contains a list of the parts and how many there should be (SOH). Somebody then manually counts the parts and then writes the actual number
onto the report. Somebody else then takes the information from the report and enters it into the database.

This works fine but I want to implement a better method by using a cordless hand scanner to scan either the Location Barcode or the Part Barcode.

I am in the early stages of designing the form that will accept the output from the hand scanner.

The form, fStocktakeUpdate_34, has been designed to be primarily driven by a touch screen and displays the various records as a Continuous Form and also displays am image that is stored externally as a JPG but I have removed
this from the sample database..

There is a text box called "txtSearchMain" that has some VBA code in the On Key Up event that is doing my head in a bit.

Basically, the Location barcodes start with a "LOC-" prefix and the Parts barcodes start with a "SKU-" prefix.

I want to use the same barcode scanner to scan either the Location of Parts barcode into the "txtSearchMain" text box and have the VBA code in the On Key Up event decide if it start starts with LOC- or SKU- and direct it to the correct
VBA code.

The bacode scanning is working fine BUT the logic in the On Key Up event is not working consistently when the SKU- barcode is scanned.

If you look at the [BarcodeNymberRaw] field in the Parts table you will see that there is a SKU-00001.

If you manually type this into the "txtSearchMain" text box you get an error. Yet if you type in SKU-01072 with works fine and locates the correct part.

I just can't seem to figure out what the issue is and hope somebody can help me out.


Cheers
Greg
 

Attachments

  • XXLateral.zip
    664.4 KB · Views: 478

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:48
Joined
May 7, 2009
Messages
19,169
maybe move your keyup code to Change event of the textbox.
 

Lateral

Registered User.
Local time
Today, 14:48
Joined
Aug 28, 2013
Messages
388
Thanks for the quick reply.
How will this fix the issue of finding only some is the SKU numbers?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:48
Joined
May 7, 2009
Messages
19,169
review the code on the Change event of both textbox.
 

Attachments

  • XXLateral.zip
    946.6 KB · Views: 523

Lateral

Registered User.
Local time
Today, 14:48
Joined
Aug 28, 2013
Messages
388
Thanks but when I type in sku-000 I still get the error message.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:48
Joined
May 7, 2009
Messages
19,169
review your code!
there are only 2 choices on your code.
either the "first character" is "s" or "l" (small L).
so you enter "s000" or "l000".
 

Lateral

Registered User.
Local time
Today, 14:48
Joined
Aug 28, 2013
Messages
388
Thanks again for help me with this but I am just not understanding why the following code works perfectly when using and searching the Location table but doesn't work consistently when using and searching the Parts table:

If Len(txtSearchMain.Text) > 0 Then
filterText = txtSearchMain.Text

Me.Form.filter = "[Location].[BarcodeNumberRaw] LIKE '*" & filterText & "*'"

Me.FilterOn = True
'Retain filter text in search box after refresh.
txtSearchMain.Text = filterText
txtSearchMain.SelStart = Len(txtSearchMain.Text)
Me.txtFilterCount = Me.RecordsetClone.RecordCount

Else
' Remove filter.
Me.filter = ""
Me.FilterOn = False
txtSearchMain.SetFocus

End If


If I use the above code the search function works perfectly and finds all of the records that have been attached to the desired Location, eg: loc-00004 or loc-00017.

If I change the code to use the Parts table then it always fails when I type sku-000 but works if I type in sku-00710:

If Len(txtSearchMain.Text) > 0 Then
filterText = txtSearchMain.Text

Me.Form.filter = "[Parts].[BarcodeNumberRaw] LIKE '*" & filterText & "*'"

Me.FilterOn = True
'Retain filter text in search box after refresh.
txtSearchMain.Text = filterText
txtSearchMain.SelStart = Len(txtSearchMain.Text)
Me.txtFilterCount = Me.RecordsetClone.RecordCount

Else
' Remove filter.
Me.filter = ""
Me.FilterOn = False
txtSearchMain.SetFocus

End If

I just don't understand what the difference is as the BarcodeNumberRaw fields are defined exactly the same as Text fields and contain almost identical data...something is different but I just can't see it.

I have stripped back the code attached to the text box used to type the search so that there is now checking for any prefix and manually change the code to use either the Parts or Location table.
Cheers
Greg
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:48
Joined
Sep 21, 2011
Messages
14,047
I keep getting
Code:
You can't reference a property or method for a control unless the control has the focus.
on the selstart, even after I set focus.?

Why are you using the .Text property and not the Value of the control?
The Raw field is also a Memo type field?
ANY error like above just tells me I have typed something in wrong?, when that is not the case?

Walk through the code to find out why.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 21:48
Joined
Sep 21, 2011
Messages
14,047
If you are wanting to use l & s to identify which field to filter, wouldn't you want to strip that off before setting the filter.?
Plus although you do have SKU-00001 in the table, it does not appear in the query the form is using.

HATH

Edit: Ignore my first sentence, blonde moment. :(
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 21:48
Joined
Sep 21, 2011
Messages
14,047
Here is my attempt, which appears to work if you know what the values are.
It will not be 100% as I do not know exactly what you are trying, but at least this does filter on both.

NB they are just fields in the same table, not different tables as you described them in the code for the query.
You still need to check what actually is in the query. I just looked at a few values in the query and used those.

I put this in the AfterUdate for the control as you can see.
I also commented out the SelStart as that was causing that error I mentioned, even if I set focus to the control first.:unsure:

Code:
Private Sub txtSearchMain_AfterUpdate()
On Error GoTo errHandler

Dim strFilter As String, strTable As String
'Check for L and search Location

'Apply or update filter based on user input.

If Left(Me.txtSearchMain, 1) = "l" Then
    strTable = "[Location.BarcodeNumberRaw]"
Else
    strTable = "[Parts.BarcodeNumberRaw]"
End If
' Now check we have something to use
If Len(Me.txtSearchMain) > 0 Then
   strFilter = Me.txtSearchMain
   'Me.Form.filter = "[Location].[LocationName] LIKE '*" & strFilter & "*' OR [Location].[LocationName] LIKE '*" & strFilter & "*'"
   Me.Form.Filter = strTable & "  LIKE '*" & strFilter & "*'"
   
   Me.FilterOn = True
  'Retain filter text in search box after refresh.
   Me.txtSearchMain = strFilter
   'Me.txtSearchMain.SelStart = Len(txtSearchMain)
   Me.txtFilterCount = Me.RecordsetClone.RecordCount
Else
   ' Remove filter.
   Me.Filter = ""
   Me.FilterOn = False
   Me.txtSearchMain.SetFocus
End If

Exit Sub

errHandler:
MsgBox Err.Number & vbCrLf & Err.Description
'MsgBox "You have typed something wrong. Double click the field to reset and start again"

End Sub

HTH
 
Last edited:

Lateral

Registered User.
Local time
Today, 14:48
Joined
Aug 28, 2013
Messages
388
Thanks Gasman,

Sorry about the issue with one of the fields being defined as a memo...this was done accidentally.

I have implemented your code but I still cannot get the sku-00004 to work...it just doesn't find it.

I have attached the database that I am using for testing.

Cheers
Greg
 

Attachments

  • XXLateral.zip
    773.7 KB · Views: 531

Gasman

Enthusiastic Amateur
Local time
Today, 21:48
Joined
Sep 21, 2011
Messages
14,047
I have already told you. Those entries DO NOT exist in the query.? Look for yourself.?

You need to walk through what the code is doing and what data is actually available, rather than what you think the code is doing and what you think is available.? The two parts of the process must match for what you require. AFAIK, that code will find an entry in the query, but only if it exists.

Look at the query output, no SKU-0001 or SKU-0004, yet both do exist in the Parts table.

The amended code was just to show you the way. No doubt it will need amending to suit your requirements.?
 

Lateral

Registered User.
Local time
Today, 14:48
Joined
Aug 28, 2013
Messages
388
Hi Gasman,

The penny has finally dropped!

I was focusing on the data in the table and not the data in the query result....all fix and now working as it should.

Thanks again for you patience and help.

Cheers
Greg
 

Users who are viewing this thread

Top Bottom