S
SpiffW
The task at hand is that someone will scan multiple barcodes (40 max)
and the system will output whether they are Hot, Warm, or Cold items.
My current solution is to have a form with 40 rows of 3 text boxes.
The boxes would be named something like TxtRow1a, TxtRow1b, TxtRow1c,
TxtRow2a, TxtRow2b, TxtRow2c, etc. When a user scans a barcode into
the first text box on a row, access does it's thing and the second box
on the row is populated with the item type and the 3rd box is
populated with the item status (hot, warm, cold).
I can do this all fine and dandy, HOWEVER, with my current method I
would have to put the code into all 40 AfterUpdate() procedures for
the 1st box in each row. My current code is as follows:
-----------------------------------------------------------------
Private Sub Text1_AfterUpdate()
Dim strSQL As String
Dim loqd As QueryDef
strSQL = "SELECT TableSPSData.Barcode, TableSPSData.SPSType,
TableKitPriorityList.Priority FROM TableKitPriorityList INNER JOIN
TableSPSData ON TableKitPriorityList.SPSMaterial =
TableSPSData.SPSType WHERE TableSPSData.[Barcode]=Forms!
[FormPriorityList]![TextRow1a];"
Set loqd = CurrentDb.QueryDefs("QueryReturnTypeAndPriority")
loqd.SQL = strSQL
loqd.Close
'DoCmd.OpenQuery "QueryReturnTypeAndPriority"
Me.TextRow1b.Value = DLookup("[SPSType]",
"[QueryReturnTypeAndPriority]")
Me.TextRow1c.Value = DLookup("[Priority]",
"[QueryReturnTypeAndPriority]")
If Me.TexRowt1c.Value = "HOT" Then
Me.TextRow1a.BackColor = 8388863
Me.TextRow1a.ForeColor = 16777215
Me.TextRow1b.BackColor = 8388863
Me.TextRow1b.ForeColor = 16777215
Me.TextRow1c.BackColor = 8388863
Me.TextRow1c.ForeColor = 16777215
End If
End Sub
------------------------------------------------
First of all, how can I pass a variable to a line of code like:
Me.TextRow1c.Value = DLookup("[Priority]",
"[QueryReturnTypeAndPriority]")
instead of using the actual text box name (TextRow1c, in this case).
My initial idea was that I could have:
---------------------------------------------------------
Dim SomeVar as String
Dim SomeVarB as String
Dim SomeVarC as String
SomeVar = Me.ActiveControl.Name
SomeVarB = SomeVar & "b"
SomeVarC = SomeVar & "c"
--------------------------------------------------------------
Is there any light at the end of this tunnel?
Are there any completely obvious ways that I could go about this in a
better way? I have no formal access/vba training so any critiquing of
the actual code/format/approach would be appreciated.
Thanks,
Steve
and the system will output whether they are Hot, Warm, or Cold items.
My current solution is to have a form with 40 rows of 3 text boxes.
The boxes would be named something like TxtRow1a, TxtRow1b, TxtRow1c,
TxtRow2a, TxtRow2b, TxtRow2c, etc. When a user scans a barcode into
the first text box on a row, access does it's thing and the second box
on the row is populated with the item type and the 3rd box is
populated with the item status (hot, warm, cold).
I can do this all fine and dandy, HOWEVER, with my current method I
would have to put the code into all 40 AfterUpdate() procedures for
the 1st box in each row. My current code is as follows:
-----------------------------------------------------------------
Private Sub Text1_AfterUpdate()
Dim strSQL As String
Dim loqd As QueryDef
strSQL = "SELECT TableSPSData.Barcode, TableSPSData.SPSType,
TableKitPriorityList.Priority FROM TableKitPriorityList INNER JOIN
TableSPSData ON TableKitPriorityList.SPSMaterial =
TableSPSData.SPSType WHERE TableSPSData.[Barcode]=Forms!
[FormPriorityList]![TextRow1a];"
Set loqd = CurrentDb.QueryDefs("QueryReturnTypeAndPriority")
loqd.SQL = strSQL
loqd.Close
'DoCmd.OpenQuery "QueryReturnTypeAndPriority"
Me.TextRow1b.Value = DLookup("[SPSType]",
"[QueryReturnTypeAndPriority]")
Me.TextRow1c.Value = DLookup("[Priority]",
"[QueryReturnTypeAndPriority]")
If Me.TexRowt1c.Value = "HOT" Then
Me.TextRow1a.BackColor = 8388863
Me.TextRow1a.ForeColor = 16777215
Me.TextRow1b.BackColor = 8388863
Me.TextRow1b.ForeColor = 16777215
Me.TextRow1c.BackColor = 8388863
Me.TextRow1c.ForeColor = 16777215
End If
End Sub
------------------------------------------------
First of all, how can I pass a variable to a line of code like:
Me.TextRow1c.Value = DLookup("[Priority]",
"[QueryReturnTypeAndPriority]")
instead of using the actual text box name (TextRow1c, in this case).
My initial idea was that I could have:
---------------------------------------------------------
Dim SomeVar as String
Dim SomeVarB as String
Dim SomeVarC as String
SomeVar = Me.ActiveControl.Name
SomeVarB = SomeVar & "b"
SomeVarC = SomeVar & "c"
--------------------------------------------------------------
Is there any light at the end of this tunnel?
Are there any completely obvious ways that I could go about this in a
better way? I have no formal access/vba training so any critiquing of
the actual code/format/approach would be appreciated.
Thanks,
Steve