Can stLinkCriteria be used to search multiple rows to find a match ? 2

[testkitt2]

Hello to all
I have a small DB which tracks tires by a number (barcode) stamped on the tire.

1.Barcode (stamped on tire)
2.Location (examples are a truck, in inventory or out for repair).
3.Position (Is the position on the truck...left front, right front....etc).

(formname is "frmTPosSide")
currently I have a popup form who has 2 statements.
on the forms record source is a (SQL)query statement:

SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Right Front], tblTirePosition.[Left Front], tblTirePosition.[Right Rear Outer], tblTirePosition.[Right Rear Inner], tblTirePosition.[Left Rear Inner], tblTirePosition.[Left Rear Outer], tblTirePosition.[Right Rear Rear Outer], tblTirePosition.[Right Rear Rear Inner], tblTirePosition.[Left Rear Rear Inner], tblTirePosition.[Left Rear Rear Outer], * FROM tblTirePosition WHERE ((([Enter BarCode Number ?]) In ([Right Front],[Left Front],[Right Rear Outer],[Right Rear Inner],[Left Rear Inner],[Left Rear Outer],[Right Rear Rear Outer],[Right Rear Rear Inner],[Left Rear Rear Inner],[Left Rear Rear Outer])));

In query builder in criteria is:

In ([Right Front],[Left Front],[Right Rear Outer],[Right Rear Inner],[Left Rear Inner],[Left Rear Outer],[Right Rear Rear Outer],[Right Rear Rear Inner],[Left Rear Rear Inner],[Left Rear Rear Outer])

When you trigger frmTPosSide it asks the user for a barcode number, If the barcode is found it tells the user what truck its on and what position.
sorry for writing a book...
I want the "frmTPosSide" form to link to the main form "frmTireCerticate" by a button or by double clicking in the barcode textbox.
Any help from you guys will definitely be appreciated.
Thank you
JZ



Testkitt2

 

[Remou]

You can use the Double_Click event to open the form:

[tt]DoCmd.OpenForm "frmTireCerticate",,,"Barcode='" & Me.Barcode & "'"[/tt]

Assuming that barcode is a text field.

tblTirePosition does not appear to be normalized.

 

[testkitt2]

Thanks Remou
for your input.
The qry code as stated above currently links criteria by a barcode number. Enter a barcode to search for:...It brings up a small form shaped like a truck and shows truck number and barcodes currently on the truck. (error to trap barcode not found). You see the main frm is where the barcode gets its birth certificate and as you enter the barcode number and other info..when you get to the textbox called "location" and if you enter a truck number (other locations could be "inventory" or at vendor) then a popup form comes up..with strlinkcriteria code by truck number. If its a new truck then on popup you can enter the truck number and then enter that barcode in one of 14 textboxes for which it pertains..such as right front...left front etc.. the form I made is shaped like a trucks frame and label as stated. RF..LF...RRF. What I want to do instead of aside from this popup which does pretty much what I want it to do...is this.. In the main form..when I click my search button for a barcode and the record comes up ..I can then double click on the textbox called barcode and something like the above query will run...bringing up "frmTPosSide" and this time strlink criteria is barcode to barcode..with the focus on the popup form on the position of the barcode in the main form...The thing that gets me is that a barcode could be in one of 14 different positions.. how can I set up the query above to work off the "barcode" textbox in the mainform. again the query above currently works by clicking a cmd button and then entering the barcode to search for.
Any help or suggestions is definitely appreciated... I'm stuck.
Thanks to you all again.
JZ

Testkitt2

 

[Remou]

If I understand you correctly, you should be able to concatenate the barcodes and use Like:

[Code Typed, not tested]Set rs=Me.RecordsetClone
rs.FindFirst "RF & ' ' & LF ... & ' ' & RRF Like '*" & Me.txtSearch & "*'"

If Not rs.NoMatch Then
Me.Bookmark=rs.Bookmark
'Find the control that has the barcode
For Each ctl in Me.Controls
If ctl.ControlType=acTextbox Then
'If the search box is on the form ...
If ctl.Name<>"txtSearch" And ctl=txtSearch
ctl.SetFocus
End If
End If
Next
Else
'Not found
End If[/code]

 

[testkitt2]

popup form called frmTP
has textbox for truck number called "Fleet_Number"
and then textboxes holding the barcodes for one truck..txtbox names as follows:
'[Right Front]
'[Left Front]
'[Right Tag axle Outer]
'[Right Tag axle Inner]
'[Left Tag axle Inner]
'[Left Tag axle Outer]
'[Right Rear Outer]
'[Right Rear Inner]
'[Left Rear Inner]
'[Left Rear Outer]
'[Right Rear Rear Outer]
'[Right Rear Rear Inner]
'[Left Rear Rear Inner]
'[Left Rear Rear Outer]
*******
Main form called frmTireCertificate.
search button on form only for main form to find a barcode, barcode number appears in textbox called "barcode".
now
I want to double click on textbox in (mainform)called "barcode" and it loads (popUp)called "frmTP" so that the barcode # on main form will bring up a matched barcode # in popup form and focus will be in the textbox that has the match.

Code below was entered in the "On Open" in popup form.

Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As TextBox

  Set rs = Me.RecordsetClone

rs.FindFirst "[Right Front] & ' ' & [Left Front] ... & ' ' & [Right Tag axle Outer] Like '*" & Forms!frmTireCertificate!BarCode & "*'"

If Not rs.NoMatch Then
   Me.Bookmark = rs.Bookmark
   'Find the control that has the barcode
   For Each ctl In Me.Controls
      If ctl.ControlType = Forms!frmTireCertificate!BarCode Then
         'If the search box is on the form ...
         If ctl.Name <> "Forms!frmTireCertificate!BarCode" And ctl = Forms!frmTireCertificate!BarCode Then
            ctl.SetFocus
         End If
      End If
    Next
Else
   'Not found
End If
End Sub

it came up with a few different errors as I changed the code abit.
I'm not that good with code.. please lend some advise.
Thank you
JZ

Testkitt2

 

[Remou]

Ok. Your set up is not quite what I thought.

Sub BarCode_DblClick(Cancel As Integer)
Dim ctl As Control
DoCmd.OpenForm "frmTP", , , "[Right Front] & ' ' & [Left Front] ... & ' ' & [Right Tag axle Outer] Like '*" & Me.BarCode & "*'"

For Each ctl In Forms!frmTP.Controls
    If ctl.ControlType = acTextBox Then
        If ctl = Me.BarCode Then
            DoCmd.GoToControl ctl.Name
        End If
    End If
Next

End Sub

 

[testkitt2]

Remou thanks for your time on this one.

I applied the code you suggested and receive the following error:
Run- time error 3075
Syntax error in query expression '[right front]& ''&[left front]...&''&[right tag axle outer]like '*2629*"

Thanks
JZ

Testkitt2

 

[Remou]

Please post the entire line showing all fourteen positions.

 

[testkitt2]

Remou
right now the frmTP form comes up but with all text boxes returning blank data.. anyway

Here's where I'm using all 14 positions to change color attrib on (the got) and (loss) focus...but are the same 14 you ask me to post.

[Right Front].BackColor = RGB(255, 204, 0)
[Right Front].ForeColor = RGB(0, 0, 0)
[Left Front].BackColor = RGB(255, 204, 0)
[Left Front].ForeColor = RGB(0, 0, 0)

[Right Tag axle Outer].BackColor = RGB(255, 204, 0)
[Right Tag axle Outer].ForeColor = RGB(0, 0, 0)
[Right Tag axle Inner].BackColor = RGB(255, 204, 0)
[Right Tag axle Inner].ForeColor = RGB(0, 0, 0)

[Left Tag axle Inner].BackColor = RGB(255, 204, 0)
[Left Tag axle Inner].ForeColor = RGB(0, 0, 0)
[Left Tag axle Outer].BackColor = RGB(255, 204, 0)
[Left Tag axle Outer].ForeColor = RGB(0, 0, 0)

[Right Rear Outer].BackColor = RGB(255, 204, 0)
[Right Rear Outer].ForeColor = RGB(0, 0, 0)
[Right Rear Inner].BackColor = RGB(255, 204, 0)
[Right Rear Inner].ForeColor = RGB(0, 0, 0)

[Left Rear Inner].BackColor = RGB(255, 204, 0)
[Left Rear Inner].ForeColor = RGB(0, 0, 0)
[Left Rear Outer].BackColor = RGB(255, 204, 0)
[Left Rear Outer].ForeColor = RGB(0, 0, 0)

[Right Rear Rear Outer].BackColor = RGB(255, 204, 0)
[Right Rear Rear Outer].ForeColor = RGB(0, 0, 0)
[Right Rear Rear Inner].BackColor = RGB(255, 204, 0)
[Right Rear Rear Inner].ForeColor = RGB(0, 0, 0)

[Left Rear Rear Inner].BackColor = RGB(255, 204, 0)
[Left Rear Rear Inner].ForeColor = RGB(0, 0, 0)
[Left Rear Rear Outer].BackColor = RGB(255, 204, 0)
[Left Rear Rear Outer].ForeColor = RGB(0, 0, 0)

Thank you

Testkitt2

 

[Remou]

I mean post this line:
[tt]DoCmd.OpenForm "frmTP", , , "[Right Front] & ' ' & [Left Front] ... & ' ' & [Right Tag axle Outer] Like '*" & Me.BarCode & "*'"[/tt]

Edited to show all the positions. You have edited it, haven't you?

 

[testkitt2]

Remou
Sorry, I did not understand want you meant...but actually the line of code is exactly

DoCmd.OpenForm "frmTP", , , "[Right Front] & ' ' & [Left Front] ... & ' ' & [Right Tag axle Outer] Like '*" & Me.BarCode & "*'"

what you posted as an example. since you mentioned concatenate and my coding ..not good at all...I tried it.
My Bad
JZ

Testkitt2

 

[testkitt2]

good eve to all
I'm still trying to put this code together...need some help...
Thank you
JZ

Testkitt2

 

[Remou]

Please post the line back, modified to show all fourteen positions.

 

[testkitt2]

Hello Remou
see code below:

Private Sub BarCode_DblClick(Cancel As Integer)
Dim ctl As Control
DoCmd.OpenForm "frmTP", , , "[Right Front] & ' ' & [Left Front] & ' ' & [Right Tag axle Outer] & ' ' & [Right Tag axle Inner] & ' '& [Left Tag axle Inner] & ' '& [Left Tag axle Outer] & ' '& [Right Rear Outer] & ' '& [Right Rear Inner] & ' '& [Left Rear Inner] & ' '& [Left Rear Outer] & ' '& [Right Rear Rear Outer] & ' '& [Right Rear Rear Inner] & ' '&& [Left Rear Rear Inner] & ' '& [Left Rear Rear Outer] & ' ' Like '*" & Me.BarCode & "*'"
For Each ctl In Forms!frmTP.Controls
    If ctl.ControlType = acTextBox Then
        If ctl = Me.BarCode Then
            DoCmd.GoToControl ctl.Name
        End If
    End If
Next
End Sub

Returned this error..
Run time error 3075
Syntax error (missing operator) in query expression '[Right Front]& ' ' & [Left Front] & ' ' etc etc...

Thank for your help.
JZ

Testkitt2

 

[PHV]

Replace this:
& ' '&&
with this:
& ' ' &

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[testkitt2]

Thank you Remou and PHV
The code is working...
Below is a summary of what I ended up with..
again thanks much for all your help.

***************************

On the main form...double clicking on the txtbox field called "barcode"...(which contains the "barcode" I want to match with a popup form)..here's the code..provided by you guys..

Private Sub BarCode_DblClick(Cancel As Integer)
Dim ctl As Control
'************
On Error GoTo Err_BarCode_DblClick
 
    If Not IsNumeric(Me![Location]) Then
 GoTo Err_BarCode_DblClick
 End If
'************

DoCmd.OpenForm "frmTP", , , "[Right Front] & ' ' & [Left Front] & ' ' & [Right Tag axle Outer] & ' ' & [Right Tag axle Inner] & ' '& [Left Tag axle Inner] & ' '& [Left Tag axle Outer] & ' '& [Right Rear Outer] & ' '& [Right Rear Inner] & ' '& [Left Rear Inner] & ' '& [Left Rear Outer] & ' '& [Right Rear Rear Outer] & ' '& [Right Rear Rear Inner] & ' '& [Left Rear Rear Inner] & ' '& [Left Rear Rear Outer] & ' ' Like '*" & Me.BarCode & "*'"

For Each ctl In Forms!frmTP.Controls
    If ctl.ControlType = acTextBox Then
        If ctl = Me.BarCode Then
            DoCmd.GoToControl ctl.Name
        End If
    End If
Next
Exit_BarCode_DblClick:
    Exit Sub

Err_BarCode_DblClick:
  MsgBox "Current Location is not a Truck !", vbExclamation, "DB Administrator"
End Sub

[\code]


below is the properties on the popup form that is triggered by double clicking on the "Textbox" called "Barcode" on the main form
Record source:
SELECT * FROM tblTirePosition INNER JOIN tblEquipData ON [tblTirePosition].[Fleet Number]=[tblEquipData].[Truck Number]; 
Filter:
[Right Front] & ' ' & [Left Front] & ' ' & [Right Tag axle Outer] & ' ' & [Right Tag axle Inner] & ' '& [Left Tag axle Inner] & ' '& [Left Tag axle Outer] & ' '& [Right Rear Outer] & ' '& [Right Rear Inner] & ' '& [Left Rear Inner] & ' '& [Left Rear Outer] & ' '& [Right Rear Rear Outer] & ' '& [Right Rear Rear Inner] & ' '& [Left Rear Rear Inner] & ' '& [Left Rear Rear Outer] & ' ' Like '*2326*'

JZ

Testkitt2