Use of a bar code scanner - quickest update possible?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I have a form that has an entry field where I will be using a barcode
scanner to enter an item number. Once the number is inserted into the field,
I would like to update certain records within the items table, bases on the
item number inserted. What would be the fastest search method to use to look
up the item number and make the update? DLookup? FindRecord? If someone
could provide any examples, I would be very appreciative, thanks!
-gary
 
Gary Dolliver said:
Hi all,
I have a form that has an entry field where I will be using a barcode
scanner to enter an item number. Once the number is inserted into the field,
I would like to update certain records within the items table, bases on the
item number inserted. What would be the fastest search method to use to look
up the item number and make the update? DLookup? FindRecord? If someone
could provide any examples, I would be very appreciative, thanks!
-gary
Click to expand...

Hi Gary,
It sounds like you want to return all records in your items table and make
manual adjustments to them once you've scanned in the barcode. If this is the
case, you could have a mainform which contains the field of the item number
and then a subform linked to it that shows all your item details. The field
on the main form would be a search field (FindRecord), and once scanned, the
results would show up below.
You mentioned fastest search method. Make sure your item number field is
indexed. To save time, you might be able to set your scanner up to add a
carriage return or line feed entry at the end of each scan which will save
you hitting Enter each time.
Hope that was of some help.
Dave
 
Hi Gary,

How about simply making the ItemNumber a combobox with other columns for
the info you want to echo?

in another control, you can display info from other columns like this:

=combobox_controlname.column(1)

where this is really the second column since indexing starts with 0

you can also, of course, grab this value in code too:
Me.combobox_controlname.column(1)

why are you wanting to update another table? You are not storing
calculated fields, are you?

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Hi Gary,

if you are using the scanned number in an unbound field to find records
to update, do this:

base your form on the table you want to change

make an unbound textbox in the form header and then, on its AfterUpdate
event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mItemNumber As String

'set value to look up by what is selected
mItemNumber = Me.ActiveControl

'clear the choice to find
'comment this out if you want it to keep showing
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "ItemNumber_fieldname = '" _
& mItemNumber & "'"

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~~~~~~~
where
-ItemNumber_fieldname is the name of the field containing the Item
Number. If it is a number and not text, remove the quote delimiters and
change the data type is is DIMensioned as. Make sure this field is in
the form's RecordSource

-ProcedureName is the name of the procedure that the code is in so if
there is an error, you can see you can see where it is
Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Thank you Dave! I completely forgot about indexing! However, is there a way
to do this if I am using MySQL tables? I am guessing this would need to be
done through the MySQL administrator or Query browser...
I have the scanner already putting in the carriage return, which seems to
work well, I was just getting concerned as I now have about 1500 records and
it seems to be getting slower. Here is some more information that was
probably needed earlier ;)
Basically, this is an item check in form to track the production of various
items, all with uniqu item_id - which is what is scanned.
All of these items may go through different production steps, which is
determined in a combination of dropdowns before scanning. Once we get
through the dropdowns, we scan the item number and it will first check to
make sure the item being scanned matches the above dropdown criteria. If it
passes, it then will update the item record with a date and employee being
inserted into the step that was selected above. I am currently using the
following code (sorry, this is going to be lengthy)... I am now thinking
that my code may be the issue as there is so much of it!

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set con = CurrentProject.Connection 'Control Name -
[Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]
Set rst = New ADODB.Recordset

rst.Open "Items", con, adOpenKeyset, adLockOptimistic 'open the table Items
If IsNull([Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]) Then 'in
case Item_ID field is null
MsgBox "No Item ID to Update", vbCritical, "ERROR"
Exit Sub
Else ' if Item_ID is not null
rst.Find "Item_ID = " &
Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID], , adSearchForward

End If
Debug.Print rst!Item_ID 'check Item_ID - to delete “Debug.Print†after use

If Type1.Value = 1 Then
'need to double check item_type_id to make sure correct info is passed across
If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9 Then
If Step.Value = "Receiving" Then
If IsNull(rst!Receiving) Then 'check Recieving isNull?
rst!Receiving = Now()
rst![Received By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Received - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Scanning" Then
If IsNull(rst!Scanning) Then
rst!Scanning = Now()
rst![Scanned By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Scanned - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Printing" Then
If IsNull(rst!Printing) Then
rst!Printing = Now()
rst![Printed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Printed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Assembly" Then
If IsNull(rst!Assembly) Then
rst!Assembly = Now()
rst![Assembled By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Assembled - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Trimming" Then
If IsNull(rst!Trimming) Then
rst!Trimming = Now()
rst![Trimmed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Trimmed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Shipping" Then
If IsNull(rst!Shipping) Then
rst!Shipping = Now()
rst![Shipped By] = CurrentUser
rst.Update
MsgBox "Update Successful"
DoCmd.RunSQL "INSERT INTO Ship_Order (Item_ID, Shipper,
Ship_Scan_Date) VALUES (Item_ID, CurrentUser, Now())"
Else
DoCmd.Beep
MsgBox "Item has already been Shipped - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
End If
Else
MsgBox ("Error - Incorrect Item Type Being Scanned")
End If


then it will check if the item is Type2..., then Type3... and so on

Thank you again for your help!!

-gary
 
Hi Crystal!
I tried using a combo box instead of a regular text box, but it seemed to
take the same amount of time to update, which was discouraging... I am
wanting to update the table with a date and employee for a specific
production step that an item is going through, which to my knowledge is not a
calculated field, just a date and name.
What I am beginning to think is that I have too much code to verify too many
things about the item being scanned... I am including part of my current
code below.


Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set con = CurrentProject.Connection 'Control Name -
[Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]
Set rst = New ADODB.Recordset

rst.Open "Items", con, adOpenKeyset, adLockOptimistic 'open the table Items
If IsNull([Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]) Then 'in
case Item_ID field is null
MsgBox "No Item ID to Update", vbCritical, "ERROR"
Exit Sub
Else ' if Item_ID is not null
rst.Find "Item_ID = " &
Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID], , adSearchForward

End If
Debug.Print rst!Item_ID 'check Item_ID - to delete “Debug.Print†after use

If Type1.Value = 1 Then
'need to double check item_type_id to make sure correct info is passed across
If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9 Then
If Step.Value = "Receiving" Then
If IsNull(rst!Receiving) Then 'check Recieving isNull?
rst!Receiving = Now()
rst![Received By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Received - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Scanning" Then
If IsNull(rst!Scanning) Then
rst!Scanning = Now()
rst![Scanned By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Scanned - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Printing" Then
If IsNull(rst!Printing) Then
rst!Printing = Now()
rst![Printed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Printed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Assembly" Then
If IsNull(rst!Assembly) Then
rst!Assembly = Now()
rst![Assembled By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Assembled - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Trimming" Then
If IsNull(rst!Trimming) Then
rst!Trimming = Now()
rst![Trimmed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Trimmed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Shipping" Then
If IsNull(rst!Shipping) Then
rst!Shipping = Now()
rst![Shipped By] = CurrentUser
rst.Update
MsgBox "Update Successful"
DoCmd.RunSQL "INSERT INTO Ship_Order (Item_ID, Shipper,
Ship_Scan_Date) VALUES (Item_ID, CurrentUser, Now())"
Else
DoCmd.Beep
MsgBox "Item has already been Shipped - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
End If
Else
MsgBox ("Error - Incorrect Item Type Being Scanned")
End If

Now it will begin an If for Type2.... and then Type3... and so on...

Any way to stream line this?? ha!

thanks!
-gary
 
Hi Gary,

dLookup is slow -- so you should only do it once and assign its return
to a variable

this statement, for instance, has 2 of them:

If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9

instead, consider this:

'~~~~~~~~~~~~~~~
dim mItem_Type_ID as Long
mItem_Type_ID = nz(DLookup("Item_Type_ID", "Items", "Item_ID=" &
[Item_ID]),0)
'statements
if mItem_Type_ID = 8 or mItem_Type_ID = 9 then
'statements
'~~~~~~~~~~~~~~~

I wrapped the result in nz (null-to-zero) in case dLookup didn't find
anything -- otherwise, you will have an error if it doesn't.

~~~

"update the table with a date and employee for a specific
production step "

it looks as though your data is not properly structured. Rather than
having fields for each step ... consider this:

*StepTypes*
StepTypeID, autonumber
Step, text -- receiving, scanning, etc

*ProductionSteps*
StepID, autonumber
someID, long integer -- foreign key to relate to the main record
StepTypeID, long integer -- FK to StepTypes
StepDate, date/time
StepUser, text

By doing this, you will save yourself a LOT of effort when it comes to
reporting and tracking too. More time spent in setting up a good
structures saves countless headaches and problems down the road...

to help you understand how to structure data in Access, send me an email
and request my 30-page Word document on Access Basics (for Programming)
-- it doesn't cover VBA, but prepares you for it because it covers
essentials in Access. Be sure to put "Access Basics" in the subject
line so that I see your message...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary said:
Hi Crystal!
I tried using a combo box instead of a regular text box, but it seemed to
take the same amount of time to update, which was discouraging... I am
wanting to update the table with a date and employee for a specific
production step that an item is going through, which to my knowledge is not a
calculated field, just a date and name.
What I am beginning to think is that I have too much code to verify too many
things about the item being scanned... I am including part of my current
code below.


Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set con = CurrentProject.Connection 'Control Name -
[Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]
Set rst = New ADODB.Recordset

rst.Open "Items", con, adOpenKeyset, adLockOptimistic 'open the table Items
If IsNull([Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]) Then 'in
case Item_ID field is null
MsgBox "No Item ID to Update", vbCritical, "ERROR"
Exit Sub
Else ' if Item_ID is not null
rst.Find "Item_ID = " &
Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID], , adSearchForward

End If
Debug.Print rst!Item_ID 'check Item_ID - to delete “Debug.Print†after use

If Type1.Value = 1 Then
'need to double check item_type_id to make sure correct info is passed across
If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9 Then
If Step.Value = "Receiving" Then
If IsNull(rst!Receiving) Then 'check Recieving isNull?
rst!Receiving = Now()
rst![Received By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Received - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Scanning" Then
If IsNull(rst!Scanning) Then
rst!Scanning = Now()
rst![Scanned By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Scanned - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Printing" Then
If IsNull(rst!Printing) Then
rst!Printing = Now()
rst![Printed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Printed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Assembly" Then
If IsNull(rst!Assembly) Then
rst!Assembly = Now()
rst![Assembled By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Assembled - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Trimming" Then
If IsNull(rst!Trimming) Then
rst!Trimming = Now()
rst![Trimmed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Trimmed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Shipping" Then
If IsNull(rst!Shipping) Then
rst!Shipping = Now()
rst![Shipped By] = CurrentUser
rst.Update
MsgBox "Update Successful"
DoCmd.RunSQL "INSERT INTO Ship_Order (Item_ID, Shipper,
Ship_Scan_Date) VALUES (Item_ID, CurrentUser, Now())"
Else
DoCmd.Beep
MsgBox "Item has already been Shipped - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
End If
Else
MsgBox ("Error - Incorrect Item Type Being Scanned")
End If

Now it will begin an If for Type2.... and then Type3... and so on...

Any way to stream line this?? ha!

thanks!
-gary




strive4peace said:
Hi Gary,

How about simply making the ItemNumber a combobox with other columns for
the info you want to echo?

in another control, you can display info from other columns like this:

=combobox_controlname.column(1)

where this is really the second column since indexing starts with 0

you can also, of course, grab this value in code too:
Me.combobox_controlname.column(1)

why are you wanting to update another table? You are not storing
calculated fields, are you?

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Click to expand...
Click to expand...
 
Hi Crystal,
Thank you so much for all the help. As luck would have it, I am not able to
get this to work, but also, not getting any error messages. I know have the
following (this is just a snipet)

above i have all the original variable and connection openings, should these
stay the same??
~~~~~~~~~~~~~~~~~
Dim mItem_Type_ID As Long
mItem_Type_ID = Nz(DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]),
0)

If Type1.Value = 1 Then

If mItem_Type_ID = 8 Or mItem_Type_ID = 9 Then
If Step.Value = "Receiving" Then
If IsNull(rst!Receiving) Then 'check Recieving isNull?
rst!Receiving = Now()
rst![Received By] = Employee
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Error", vbInformation, "ERROR"
MsgBox "Item has already been Received - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Scanning" Then...

Sorry, what have I done wrong?
thanks!
-gary


strive4peace said:
Hi Gary,

dLookup is slow -- so you should only do it once and assign its return
to a variable

this statement, for instance, has 2 of them:

If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9

instead, consider this:

'~~~~~~~~~~~~~~~
dim mItem_Type_ID as Long
mItem_Type_ID = nz(DLookup("Item_Type_ID", "Items", "Item_ID=" &
[Item_ID]),0)
'statements
if mItem_Type_ID = 8 or mItem_Type_ID = 9 then
'statements
'~~~~~~~~~~~~~~~

I wrapped the result in nz (null-to-zero) in case dLookup didn't find
anything -- otherwise, you will have an error if it doesn't.

~~~

"update the table with a date and employee for a specific
production step "

it looks as though your data is not properly structured. Rather than
having fields for each step ... consider this:

*StepTypes*
StepTypeID, autonumber
Step, text -- receiving, scanning, etc

*ProductionSteps*
StepID, autonumber
someID, long integer -- foreign key to relate to the main record
StepTypeID, long integer -- FK to StepTypes
StepDate, date/time
StepUser, text

By doing this, you will save yourself a LOT of effort when it comes to
reporting and tracking too. More time spent in setting up a good
structures saves countless headaches and problems down the road...

to help you understand how to structure data in Access, send me an email
and request my 30-page Word document on Access Basics (for Programming)
-- it doesn't cover VBA, but prepares you for it because it covers
essentials in Access. Be sure to put "Access Basics" in the subject
line so that I see your message...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary said:
Hi Crystal!
I tried using a combo box instead of a regular text box, but it seemed to
take the same amount of time to update, which was discouraging... I am
wanting to update the table with a date and employee for a specific
production step that an item is going through, which to my knowledge is not a
calculated field, just a date and name.
What I am beginning to think is that I have too much code to verify too many
things about the item being scanned... I am including part of my current
code below.


Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set con = CurrentProject.Connection 'Control Name -
[Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]
Set rst = New ADODB.Recordset

rst.Open "Items", con, adOpenKeyset, adLockOptimistic 'open the table Items
If IsNull([Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]) Then 'in
case Item_ID field is null
MsgBox "No Item ID to Update", vbCritical, "ERROR"
Exit Sub
Else ' if Item_ID is not null
rst.Find "Item_ID = " &
Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID], , adSearchForward

End If
Debug.Print rst!Item_ID 'check Item_ID - to delete “Debug.Print†after use

If Type1.Value = 1 Then
'need to double check item_type_id to make sure correct info is passed across
If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9 Then
If Step.Value = "Receiving" Then
If IsNull(rst!Receiving) Then 'check Recieving isNull?
rst!Receiving = Now()
rst![Received By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Received - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Scanning" Then
If IsNull(rst!Scanning) Then
rst!Scanning = Now()
rst![Scanned By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Scanned - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Printing" Then
If IsNull(rst!Printing) Then
rst!Printing = Now()
rst![Printed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Printed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Assembly" Then
If IsNull(rst!Assembly) Then
rst!Assembly = Now()
rst![Assembled By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Assembled - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Trimming" Then
If IsNull(rst!Trimming) Then
rst!Trimming = Now()
rst![Trimmed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Trimmed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Shipping" Then
If IsNull(rst!Shipping) Then
rst!Shipping = Now()
rst![Shipped By] = CurrentUser
rst.Update
MsgBox "Update Successful"
DoCmd.RunSQL "INSERT INTO Ship_Order (Item_ID, Shipper,
Ship_Scan_Date) VALUES (Item_ID, CurrentUser, Now())"
Else
DoCmd.Beep
MsgBox "Item has already been Shipped - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
End If
Else
MsgBox ("Error - Incorrect Item Type Being Scanned")
End If

Now it will begin an If for Type2.... and then Type3... and so on...

Any way to stream line this?? ha!

thanks!
-gary




strive4peace said:
Hi Gary,

How about simply making the ItemNumber a combobox with other columns for
the info you want to echo?

in another control, you can display info from other columns like this:

=combobox_controlname.column(1)

where this is really the second column since indexing starts with 0

you can also, of course, grab this value in code too:
Me.combobox_controlname.column(1)

why are you wanting to update another table? You are not storing
calculated fields, are you?

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi all,
I have a form that has an entry field where I will be using a barcode
scanner to enter an item number. Once the number is inserted into the field,
I would like to update certain records within the items table, bases on the
item number inserted. What would be the fastest search method to use to look
up the item number and make the update? DLookup? FindRecord? If someone
could provide any examples, I would be very appreciative, thanks!
-gary
Click to expand...
Click to expand...
Click to expand...
 
Hi Gary,

try
rst.Edit

before you change any values ...ie: rst!Receiving = Now()...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary said:
Hi Crystal,
Thank you so much for all the help. As luck would have it, I am not able to
get this to work, but also, not getting any error messages. I know have the
following (this is just a snipet)

above i have all the original variable and connection openings, should these
stay the same??
~~~~~~~~~~~~~~~~~
Dim mItem_Type_ID As Long
mItem_Type_ID = Nz(DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]),
0)

If Type1.Value = 1 Then

If mItem_Type_ID = 8 Or mItem_Type_ID = 9 Then
If Step.Value = "Receiving" Then
If IsNull(rst!Receiving) Then 'check Recieving isNull?
rst!Receiving = Now()
rst![Received By] = Employee
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Error", vbInformation, "ERROR"
MsgBox "Item has already been Received - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Scanning" Then...

Sorry, what have I done wrong?
thanks!
-gary


strive4peace said:
Hi Gary,

dLookup is slow -- so you should only do it once and assign its return
to a variable

this statement, for instance, has 2 of them:

If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9

instead, consider this:

'~~~~~~~~~~~~~~~
dim mItem_Type_ID as Long
mItem_Type_ID = nz(DLookup("Item_Type_ID", "Items", "Item_ID=" &
[Item_ID]),0)
'statements
if mItem_Type_ID = 8 or mItem_Type_ID = 9 then
'statements
'~~~~~~~~~~~~~~~

I wrapped the result in nz (null-to-zero) in case dLookup didn't find
anything -- otherwise, you will have an error if it doesn't.

~~~

"update the table with a date and employee for a specific
production step "

it looks as though your data is not properly structured. Rather than
having fields for each step ... consider this:

*StepTypes*
StepTypeID, autonumber
Step, text -- receiving, scanning, etc

*ProductionSteps*
StepID, autonumber
someID, long integer -- foreign key to relate to the main record
StepTypeID, long integer -- FK to StepTypes
StepDate, date/time
StepUser, text

By doing this, you will save yourself a LOT of effort when it comes to
reporting and tracking too. More time spent in setting up a good
structures saves countless headaches and problems down the road...

to help you understand how to structure data in Access, send me an email
and request my 30-page Word document on Access Basics (for Programming)
-- it doesn't cover VBA, but prepares you for it because it covers
essentials in Access. Be sure to put "Access Basics" in the subject
line so that I see your message...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary said:
Hi Crystal!
I tried using a combo box instead of a regular text box, but it seemed to
take the same amount of time to update, which was discouraging... I am
wanting to update the table with a date and employee for a specific
production step that an item is going through, which to my knowledge is not a
calculated field, just a date and name.
What I am beginning to think is that I have too much code to verify too many
things about the item being scanned... I am including part of my current
code below.


Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set con = CurrentProject.Connection 'Control Name -
[Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]
Set rst = New ADODB.Recordset

rst.Open "Items", con, adOpenKeyset, adLockOptimistic 'open the table Items
If IsNull([Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]) Then 'in
case Item_ID field is null
MsgBox "No Item ID to Update", vbCritical, "ERROR"
Exit Sub
Else ' if Item_ID is not null
rst.Find "Item_ID = " &
Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID], , adSearchForward

End If
Debug.Print rst!Item_ID 'check Item_ID - to delete “Debug.Print†after use

If Type1.Value = 1 Then
'need to double check item_type_id to make sure correct info is passed across
If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9 Then
If Step.Value = "Receiving" Then
If IsNull(rst!Receiving) Then 'check Recieving isNull?
rst!Receiving = Now()
rst![Received By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Received - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Scanning" Then
If IsNull(rst!Scanning) Then
rst!Scanning = Now()
rst![Scanned By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Scanned - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Printing" Then
If IsNull(rst!Printing) Then
rst!Printing = Now()
rst![Printed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Printed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Assembly" Then
If IsNull(rst!Assembly) Then
rst!Assembly = Now()
rst![Assembled By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Assembled - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Trimming" Then
If IsNull(rst!Trimming) Then
rst!Trimming = Now()
rst![Trimmed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Trimmed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Shipping" Then
If IsNull(rst!Shipping) Then
rst!Shipping = Now()
rst![Shipped By] = CurrentUser
rst.Update
MsgBox "Update Successful"
DoCmd.RunSQL "INSERT INTO Ship_Order (Item_ID, Shipper,
Ship_Scan_Date) VALUES (Item_ID, CurrentUser, Now())"
Else
DoCmd.Beep
MsgBox "Item has already been Shipped - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
End If
Else
MsgBox ("Error - Incorrect Item Type Being Scanned")
End If

Now it will begin an If for Type2.... and then Type3... and so on...

Any way to stream line this?? ha!

thanks!
-gary




:

Hi Gary,

How about simply making the ItemNumber a combobox with other columns for
the info you want to echo?

in another control, you can display info from other columns like this:

=combobox_controlname.column(1)

where this is really the second column since indexing starts with 0

you can also, of course, grab this value in code too:
Me.combobox_controlname.column(1)

why are you wanting to update another table? You are not storing
calculated fields, are you?

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi all,
I have a form that has an entry field where I will be using a barcode
scanner to enter an item number. Once the number is inserted into the field,
I would like to update certain records within the items table, bases on the
item number inserted. What would be the fastest search method to use to look
up the item number and make the update? DLookup? FindRecord? If someone
could provide any examples, I would be very appreciative, thanks!
-gary
Click to expand...
Click to expand...
Click to expand...
 
Hi Crystal,

It turns out I had an extra "end if", but was not getting an error...?
Anyway, I noted that your previous reply did work. However, I am noticing it
is still running slow. I think the dLookup is causing the issues here and am
not sure what to work with now...?

Also, I know this is in a different post, but I have other questions out
there that have not been answered (some going on weeks), would you mind if I
asked them to you? One is in relation to emailing from access, and the other
is a reports question.

Thanks!
-gary

strive4peace said:
Hi Gary,

try
rst.Edit

before you change any values ...ie: rst!Receiving = Now()...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary said:
Hi Crystal,
Thank you so much for all the help. As luck would have it, I am not able to
get this to work, but also, not getting any error messages. I know have the
following (this is just a snipet)

above i have all the original variable and connection openings, should these
stay the same??
~~~~~~~~~~~~~~~~~
Dim mItem_Type_ID As Long
mItem_Type_ID = Nz(DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]),
0)

If Type1.Value = 1 Then

If mItem_Type_ID = 8 Or mItem_Type_ID = 9 Then
If Step.Value = "Receiving" Then
If IsNull(rst!Receiving) Then 'check Recieving isNull?
rst!Receiving = Now()
rst![Received By] = Employee
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Error", vbInformation, "ERROR"
MsgBox "Item has already been Received - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Scanning" Then...

Sorry, what have I done wrong?
thanks!
-gary


strive4peace said:
Hi Gary,

dLookup is slow -- so you should only do it once and assign its return
to a variable

this statement, for instance, has 2 of them:

If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9

instead, consider this:

'~~~~~~~~~~~~~~~
dim mItem_Type_ID as Long
mItem_Type_ID = nz(DLookup("Item_Type_ID", "Items", "Item_ID=" &
[Item_ID]),0)
'statements
if mItem_Type_ID = 8 or mItem_Type_ID = 9 then
'statements
'~~~~~~~~~~~~~~~

I wrapped the result in nz (null-to-zero) in case dLookup didn't find
anything -- otherwise, you will have an error if it doesn't.

~~~

"update the table with a date and employee for a specific
production step "

it looks as though your data is not properly structured. Rather than
having fields for each step ... consider this:

*StepTypes*
StepTypeID, autonumber
Step, text -- receiving, scanning, etc

*ProductionSteps*
StepID, autonumber
someID, long integer -- foreign key to relate to the main record
StepTypeID, long integer -- FK to StepTypes
StepDate, date/time
StepUser, text

By doing this, you will save yourself a LOT of effort when it comes to
reporting and tracking too. More time spent in setting up a good
structures saves countless headaches and problems down the road...

to help you understand how to structure data in Access, send me an email
and request my 30-page Word document on Access Basics (for Programming)
-- it doesn't cover VBA, but prepares you for it because it covers
essentials in Access. Be sure to put "Access Basics" in the subject
line so that I see your message...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi Crystal!
I tried using a combo box instead of a regular text box, but it seemed to
take the same amount of time to update, which was discouraging... I am
wanting to update the table with a date and employee for a specific
production step that an item is going through, which to my knowledge is not a
calculated field, just a date and name.
What I am beginning to think is that I have too much code to verify too many
things about the item being scanned... I am including part of my current
code below.


Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set con = CurrentProject.Connection 'Control Name -
[Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]
Set rst = New ADODB.Recordset

rst.Open "Items", con, adOpenKeyset, adLockOptimistic 'open the table Items
If IsNull([Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]) Then 'in
case Item_ID field is null
MsgBox "No Item ID to Update", vbCritical, "ERROR"
Exit Sub
Else ' if Item_ID is not null
rst.Find "Item_ID = " &
Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID], , adSearchForward

End If
Debug.Print rst!Item_ID 'check Item_ID - to delete “Debug.Print†after use

If Type1.Value = 1 Then
'need to double check item_type_id to make sure correct info is passed across
If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9 Then
If Step.Value = "Receiving" Then
If IsNull(rst!Receiving) Then 'check Recieving isNull?
rst!Receiving = Now()
rst![Received By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Received - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Scanning" Then
If IsNull(rst!Scanning) Then
rst!Scanning = Now()
rst![Scanned By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Scanned - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Printing" Then
If IsNull(rst!Printing) Then
rst!Printing = Now()
rst![Printed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Printed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Assembly" Then
If IsNull(rst!Assembly) Then
rst!Assembly = Now()
rst![Assembled By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Assembled - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Trimming" Then
If IsNull(rst!Trimming) Then
rst!Trimming = Now()
rst![Trimmed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Trimmed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Shipping" Then
If IsNull(rst!Shipping) Then
rst!Shipping = Now()
rst![Shipped By] = CurrentUser
rst.Update
MsgBox "Update Successful"
DoCmd.RunSQL "INSERT INTO Ship_Order (Item_ID, Shipper,
Ship_Scan_Date) VALUES (Item_ID, CurrentUser, Now())"
Else
DoCmd.Beep
MsgBox "Item has already been Shipped - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
End If
Else
MsgBox ("Error - Incorrect Item Type Being Scanned")
End If

Now it will begin an If for Type2.... and then Type3... and so on...

Any way to stream line this?? ha!

thanks!
-gary




:

Hi Gary,

How about simply making the ItemNumber a combobox with other columns for
the info you want to echo?

in another control, you can display info from other columns like this:

=combobox_controlname.column(1)

where this is really the second column since indexing starts with 0

you can also, of course, grab this value in code too:
Me.combobox_controlname.column(1)

why are you wanting to update another table? You are not storing
calculated fields, are you?

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi all,
I have a form that has an entry field where I will be using a barcode
scanner to enter an item number. Once the number is inserted into the field,
I would like to update certain records within the items table, bases on the
item number inserted. What would be the fastest search method to use to look
up the item number and make the update? DLookup? FindRecord? If someone
could provide any examples, I would be very appreciative, thanks!
-gary
Click to expand...
Click to expand...
Click to expand...
 
Hi Gary,

"It turns out I had an extra "end if", but was not getting an error...? "

did you Debug, Compile your code? I'll bet the compiler would have told
you some kind of error... like 'End If without block If'

"still running slow"

from looking at your code, I don't think it is just dLookup... there is
great opportunity for improvement in your data structure (that is ok,
part of the learning process, just don't hesitate to tear down and
rebuild -- better to do it now than down the road when the duct tape
gives out and you have invested even more time)... the Access Basics
document should really help you. If you want more food for thought, we
can give you more links -- there is no such thing as too much
information when you are building the foundation for something great
<smile> ... you want it to be strong

if there are other posts you want me to look at, tell me:
-forum
-subject
-date and time of initial post on thread
-time zone

I will look when I have a chance and help if I can

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary said:
Hi Crystal,

It turns out I had an extra "end if", but was not getting an error...?
Anyway, I noted that your previous reply did work. However, I am noticing it
is still running slow. I think the dLookup is causing the issues here and am
not sure what to work with now...?

Also, I know this is in a different post, but I have other questions out
there that have not been answered (some going on weeks), would you mind if I
asked them to you? One is in relation to emailing from access, and the other
is a reports question.

Thanks!
-gary

strive4peace said:
Hi Gary,

try
rst.Edit

before you change any values ...ie: rst!Receiving = Now()...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary said:
Hi Crystal,
Thank you so much for all the help. As luck would have it, I am not able to
get this to work, but also, not getting any error messages. I know have the
following (this is just a snipet)

above i have all the original variable and connection openings, should these
stay the same??
~~~~~~~~~~~~~~~~~
Dim mItem_Type_ID As Long
mItem_Type_ID = Nz(DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]),
0)

If Type1.Value = 1 Then

If mItem_Type_ID = 8 Or mItem_Type_ID = 9 Then
If Step.Value = "Receiving" Then
If IsNull(rst!Receiving) Then 'check Recieving isNull?
rst!Receiving = Now()
rst![Received By] = Employee
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Error", vbInformation, "ERROR"
MsgBox "Item has already been Received - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Scanning" Then...

Sorry, what have I done wrong?
thanks!
-gary


:

Hi Gary,

dLookup is slow -- so you should only do it once and assign its return
to a variable

this statement, for instance, has 2 of them:

If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9

instead, consider this:

'~~~~~~~~~~~~~~~
dim mItem_Type_ID as Long
mItem_Type_ID = nz(DLookup("Item_Type_ID", "Items", "Item_ID=" &
[Item_ID]),0)
'statements
if mItem_Type_ID = 8 or mItem_Type_ID = 9 then
'statements
'~~~~~~~~~~~~~~~

I wrapped the result in nz (null-to-zero) in case dLookup didn't find
anything -- otherwise, you will have an error if it doesn't.

~~~

"update the table with a date and employee for a specific
production step "

it looks as though your data is not properly structured. Rather than
having fields for each step ... consider this:

*StepTypes*
StepTypeID, autonumber
Step, text -- receiving, scanning, etc

*ProductionSteps*
StepID, autonumber
someID, long integer -- foreign key to relate to the main record
StepTypeID, long integer -- FK to StepTypes
StepDate, date/time
StepUser, text

By doing this, you will save yourself a LOT of effort when it comes to
reporting and tracking too. More time spent in setting up a good
structures saves countless headaches and problems down the road...

to help you understand how to structure data in Access, send me an email
and request my 30-page Word document on Access Basics (for Programming)
-- it doesn't cover VBA, but prepares you for it because it covers
essentials in Access. Be sure to put "Access Basics" in the subject
line so that I see your message...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi Crystal!
I tried using a combo box instead of a regular text box, but it seemed to
take the same amount of time to update, which was discouraging... I am
wanting to update the table with a date and employee for a specific
production step that an item is going through, which to my knowledge is not a
calculated field, just a date and name.
What I am beginning to think is that I have too much code to verify too many
things about the item being scanned... I am including part of my current
code below.


Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set con = CurrentProject.Connection 'Control Name -
[Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]
Set rst = New ADODB.Recordset

rst.Open "Items", con, adOpenKeyset, adLockOptimistic 'open the table Items
If IsNull([Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]) Then 'in
case Item_ID field is null
MsgBox "No Item ID to Update", vbCritical, "ERROR"
Exit Sub
Else ' if Item_ID is not null
rst.Find "Item_ID = " &
Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID], , adSearchForward

End If
Debug.Print rst!Item_ID 'check Item_ID - to delete “Debug.Print†after use

If Type1.Value = 1 Then
'need to double check item_type_id to make sure correct info is passed across
If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9 Then
If Step.Value = "Receiving" Then
If IsNull(rst!Receiving) Then 'check Recieving isNull?
rst!Receiving = Now()
rst![Received By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Received - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Scanning" Then
If IsNull(rst!Scanning) Then
rst!Scanning = Now()
rst![Scanned By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Scanned - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Printing" Then
If IsNull(rst!Printing) Then
rst!Printing = Now()
rst![Printed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Printed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Assembly" Then
If IsNull(rst!Assembly) Then
rst!Assembly = Now()
rst![Assembled By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Assembled - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Trimming" Then
If IsNull(rst!Trimming) Then
rst!Trimming = Now()
rst![Trimmed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Trimmed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Shipping" Then
If IsNull(rst!Shipping) Then
rst!Shipping = Now()
rst![Shipped By] = CurrentUser
rst.Update
MsgBox "Update Successful"
DoCmd.RunSQL "INSERT INTO Ship_Order (Item_ID, Shipper,
Ship_Scan_Date) VALUES (Item_ID, CurrentUser, Now())"
Else
DoCmd.Beep
MsgBox "Item has already been Shipped - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
End If
Else
MsgBox ("Error - Incorrect Item Type Being Scanned")
End If

Now it will begin an If for Type2.... and then Type3... and so on...

Any way to stream line this?? ha!

thanks!
-gary




:

Hi Gary,

How about simply making the ItemNumber a combobox with other columns for
the info you want to echo?

in another control, you can display info from other columns like this:

=combobox_controlname.column(1)

where this is really the second column since indexing starts with 0

you can also, of course, grab this value in code too:
Me.combobox_controlname.column(1)

why are you wanting to update another table? You are not storing
calculated fields, are you?

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi all,
I have a form that has an entry field where I will be using a barcode
scanner to enter an item number. Once the number is inserted into the field,
I would like to update certain records within the items table, bases on the
item number inserted. What would be the fastest search method to use to look
up the item number and make the update? DLookup? FindRecord? If someone
could provide any examples, I would be very appreciative, thanks!
-gary
Click to expand...
Click to expand...
Click to expand...
 
Thank you again,
I am going through all your documentation and it is really going to help me
out. I have been going back and forth on adding tables for production steps
and such, but it will still need the dLookup in table items to be sure the
correct item is being scanned in the proper field?? But definitly something
for me to look into more.

The main post I am concerned about is:
microsoft.public.access.formscoding
Sending Email from a specific address
2/2/2007 4:21 PM PST

I have not gotten any response from this one, or an earlier one with the
same question. Thanks!

-gary


strive4peace said:
Hi Gary,

"It turns out I had an extra "end if", but was not getting an error...? "

did you Debug, Compile your code? I'll bet the compiler would have told
you some kind of error... like 'End If without block If'

"still running slow"

from looking at your code, I don't think it is just dLookup... there is
great opportunity for improvement in your data structure (that is ok,
part of the learning process, just don't hesitate to tear down and
rebuild -- better to do it now than down the road when the duct tape
gives out and you have invested even more time)... the Access Basics
document should really help you. If you want more food for thought, we
can give you more links -- there is no such thing as too much
information when you are building the foundation for something great
<smile> ... you want it to be strong

if there are other posts you want me to look at, tell me:
-forum
-subject
-date and time of initial post on thread
-time zone

I will look when I have a chance and help if I can

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary said:
Hi Crystal,

It turns out I had an extra "end if", but was not getting an error...?
Anyway, I noted that your previous reply did work. However, I am noticing it
is still running slow. I think the dLookup is causing the issues here and am
not sure what to work with now...?

Also, I know this is in a different post, but I have other questions out
there that have not been answered (some going on weeks), would you mind if I
asked them to you? One is in relation to emailing from access, and the other
is a reports question.

Thanks!
-gary

strive4peace said:
Hi Gary,

try
rst.Edit

before you change any values ...ie: rst!Receiving = Now()...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi Crystal,
Thank you so much for all the help. As luck would have it, I am not able to
get this to work, but also, not getting any error messages. I know have the
following (this is just a snipet)

above i have all the original variable and connection openings, should these
stay the same??
~~~~~~~~~~~~~~~~~
Dim mItem_Type_ID As Long
mItem_Type_ID = Nz(DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]),
0)

If Type1.Value = 1 Then

If mItem_Type_ID = 8 Or mItem_Type_ID = 9 Then
If Step.Value = "Receiving" Then
If IsNull(rst!Receiving) Then 'check Recieving isNull?
rst!Receiving = Now()
rst![Received By] = Employee
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Error", vbInformation, "ERROR"
MsgBox "Item has already been Received - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Scanning" Then...

Sorry, what have I done wrong?
thanks!
-gary


:

Hi Gary,

dLookup is slow -- so you should only do it once and assign its return
to a variable

this statement, for instance, has 2 of them:

If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9

instead, consider this:

'~~~~~~~~~~~~~~~
dim mItem_Type_ID as Long
mItem_Type_ID = nz(DLookup("Item_Type_ID", "Items", "Item_ID=" &
[Item_ID]),0)
'statements
if mItem_Type_ID = 8 or mItem_Type_ID = 9 then
'statements
'~~~~~~~~~~~~~~~

I wrapped the result in nz (null-to-zero) in case dLookup didn't find
anything -- otherwise, you will have an error if it doesn't.

~~~

"update the table with a date and employee for a specific
production step "

it looks as though your data is not properly structured. Rather than
having fields for each step ... consider this:

*StepTypes*
StepTypeID, autonumber
Step, text -- receiving, scanning, etc

*ProductionSteps*
StepID, autonumber
someID, long integer -- foreign key to relate to the main record
StepTypeID, long integer -- FK to StepTypes
StepDate, date/time
StepUser, text

By doing this, you will save yourself a LOT of effort when it comes to
reporting and tracking too. More time spent in setting up a good
structures saves countless headaches and problems down the road...

to help you understand how to structure data in Access, send me an email
and request my 30-page Word document on Access Basics (for Programming)
-- it doesn't cover VBA, but prepares you for it because it covers
essentials in Access. Be sure to put "Access Basics" in the subject
line so that I see your message...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi Crystal!
I tried using a combo box instead of a regular text box, but it seemed to
take the same amount of time to update, which was discouraging... I am
wanting to update the table with a date and employee for a specific
production step that an item is going through, which to my knowledge is not a
calculated field, just a date and name.
What I am beginning to think is that I have too much code to verify too many
things about the item being scanned... I am including part of my current
code below.


Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set con = CurrentProject.Connection 'Control Name -
[Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]
Set rst = New ADODB.Recordset

rst.Open "Items", con, adOpenKeyset, adLockOptimistic 'open the table Items
If IsNull([Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]) Then 'in
case Item_ID field is null
MsgBox "No Item ID to Update", vbCritical, "ERROR"
Exit Sub
Else ' if Item_ID is not null
rst.Find "Item_ID = " &
Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID], , adSearchForward

End If
Debug.Print rst!Item_ID 'check Item_ID - to delete “Debug.Print†after use

If Type1.Value = 1 Then
'need to double check item_type_id to make sure correct info is passed across
If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9 Then
If Step.Value = "Receiving" Then
If IsNull(rst!Receiving) Then 'check Recieving isNull?
rst!Receiving = Now()
rst![Received By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Received - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Scanning" Then
If IsNull(rst!Scanning) Then
rst!Scanning = Now()
rst![Scanned By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Scanned - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Printing" Then
If IsNull(rst!Printing) Then
rst!Printing = Now()
rst![Printed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Printed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Assembly" Then
If IsNull(rst!Assembly) Then
rst!Assembly = Now()
rst![Assembled By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Assembled - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Trimming" Then
If IsNull(rst!Trimming) Then
rst!Trimming = Now()
rst![Trimmed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Trimmed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Shipping" Then
If IsNull(rst!Shipping) Then
rst!Shipping = Now()
rst![Shipped By] = CurrentUser
rst.Update
MsgBox "Update Successful"
DoCmd.RunSQL "INSERT INTO Ship_Order (Item_ID, Shipper,
Ship_Scan_Date) VALUES (Item_ID, CurrentUser, Now())"
Else
DoCmd.Beep
MsgBox "Item has already been Shipped - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
End If
Else
MsgBox ("Error - Incorrect Item Type Being Scanned")
End If

Now it will begin an If for Type2.... and then Type3... and so on...

Any way to stream line this?? ha!

thanks!
-gary
Click to expand...
Click to expand...
Click to expand...
 
Hi Gary,

you're welcome

I answered your other post

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary said:
Thank you again,
I am going through all your documentation and it is really going to help me
out. I have been going back and forth on adding tables for production steps
and such, but it will still need the dLookup in table items to be sure the
correct item is being scanned in the proper field?? But definitly something
for me to look into more.

The main post I am concerned about is:
microsoft.public.access.formscoding
Sending Email from a specific address
2/2/2007 4:21 PM PST

I have not gotten any response from this one, or an earlier one with the
same question. Thanks!

-gary


strive4peace said:
Hi Gary,

"It turns out I had an extra "end if", but was not getting an error...? "

did you Debug, Compile your code? I'll bet the compiler would have told
you some kind of error... like 'End If without block If'

"still running slow"

from looking at your code, I don't think it is just dLookup... there is
great opportunity for improvement in your data structure (that is ok,
part of the learning process, just don't hesitate to tear down and
rebuild -- better to do it now than down the road when the duct tape
gives out and you have invested even more time)... the Access Basics
document should really help you. If you want more food for thought, we
can give you more links -- there is no such thing as too much
information when you are building the foundation for something great
<smile> ... you want it to be strong

if there are other posts you want me to look at, tell me:
-forum
-subject
-date and time of initial post on thread
-time zone

I will look when I have a chance and help if I can

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary said:
Hi Crystal,

It turns out I had an extra "end if", but was not getting an error...?
Anyway, I noted that your previous reply did work. However, I am noticing it
is still running slow. I think the dLookup is causing the issues here and am
not sure what to work with now...?

Also, I know this is in a different post, but I have other questions out
there that have not been answered (some going on weeks), would you mind if I
asked them to you? One is in relation to emailing from access, and the other
is a reports question.

Thanks!
-gary

:

Hi Gary,

try
rst.Edit

before you change any values ...ie: rst!Receiving = Now()...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi Crystal,
Thank you so much for all the help. As luck would have it, I am not able to
get this to work, but also, not getting any error messages. I know have the
following (this is just a snipet)

above i have all the original variable and connection openings, should these
stay the same??
~~~~~~~~~~~~~~~~~
Dim mItem_Type_ID As Long
mItem_Type_ID = Nz(DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]),
0)

If Type1.Value = 1 Then

If mItem_Type_ID = 8 Or mItem_Type_ID = 9 Then
If Step.Value = "Receiving" Then
If IsNull(rst!Receiving) Then 'check Recieving isNull?
rst!Receiving = Now()
rst![Received By] = Employee
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Error", vbInformation, "ERROR"
MsgBox "Item has already been Received - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Scanning" Then...

Sorry, what have I done wrong?
thanks!
-gary


:

Hi Gary,

dLookup is slow -- so you should only do it once and assign its return
to a variable

this statement, for instance, has 2 of them:

If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9

instead, consider this:

'~~~~~~~~~~~~~~~
dim mItem_Type_ID as Long
mItem_Type_ID = nz(DLookup("Item_Type_ID", "Items", "Item_ID=" &
[Item_ID]),0)
'statements
if mItem_Type_ID = 8 or mItem_Type_ID = 9 then
'statements
'~~~~~~~~~~~~~~~

I wrapped the result in nz (null-to-zero) in case dLookup didn't find
anything -- otherwise, you will have an error if it doesn't.

~~~

"update the table with a date and employee for a specific
production step "

it looks as though your data is not properly structured. Rather than
having fields for each step ... consider this:

*StepTypes*
StepTypeID, autonumber
Step, text -- receiving, scanning, etc

*ProductionSteps*
StepID, autonumber
someID, long integer -- foreign key to relate to the main record
StepTypeID, long integer -- FK to StepTypes
StepDate, date/time
StepUser, text

By doing this, you will save yourself a LOT of effort when it comes to
reporting and tracking too. More time spent in setting up a good
structures saves countless headaches and problems down the road...

to help you understand how to structure data in Access, send me an email
and request my 30-page Word document on Access Basics (for Programming)
-- it doesn't cover VBA, but prepares you for it because it covers
essentials in Access. Be sure to put "Access Basics" in the subject
line so that I see your message...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Gary Dolliver wrote:
Hi Crystal!
I tried using a combo box instead of a regular text box, but it seemed to
take the same amount of time to update, which was discouraging... I am
wanting to update the table with a date and employee for a specific
production step that an item is going through, which to my knowledge is not a
calculated field, just a date and name.
What I am beginning to think is that I have too much code to verify too many
things about the item being scanned... I am including part of my current
code below.


Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set con = CurrentProject.Connection 'Control Name -
[Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]
Set rst = New ADODB.Recordset

rst.Open "Items", con, adOpenKeyset, adLockOptimistic 'open the table Items
If IsNull([Forms]![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID]) Then 'in
case Item_ID field is null
MsgBox "No Item ID to Update", vbCritical, "ERROR"
Exit Sub
Else ' if Item_ID is not null
rst.Find "Item_ID = " &
Forms![RECEIVING_PRODUCTION_Step_Check_In]![Item_ID], , adSearchForward

End If
Debug.Print rst!Item_ID 'check Item_ID - to delete “Debug.Print†after use

If Type1.Value = 1 Then
'need to double check item_type_id to make sure correct info is passed across
If DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 8 Or
DLookup("Item_Type_ID", "Items", "Item_ID=" & [Item_ID]) = 9 Then
If Step.Value = "Receiving" Then
If IsNull(rst!Receiving) Then 'check Recieving isNull?
rst!Receiving = Now()
rst![Received By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Received - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Scanning" Then
If IsNull(rst!Scanning) Then
rst!Scanning = Now()
rst![Scanned By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Scanned - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Printing" Then
If IsNull(rst!Printing) Then
rst!Printing = Now()
rst![Printed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Printed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Assembly" Then
If IsNull(rst!Assembly) Then
rst!Assembly = Now()
rst![Assembled By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Assembled - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Trimming" Then
If IsNull(rst!Trimming) Then
rst!Trimming = Now()
rst![Trimmed By] = CurrentUser
rst.Update
MsgBox "Update Successful"
Else
DoCmd.Beep
MsgBox "Item has already been Trimmed - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
ElseIf Step.Value = "Shipping" Then
If IsNull(rst!Shipping) Then
rst!Shipping = Now()
rst![Shipped By] = CurrentUser
rst.Update
MsgBox "Update Successful"
DoCmd.RunSQL "INSERT INTO Ship_Order (Item_ID, Shipper,
Ship_Scan_Date) VALUES (Item_ID, CurrentUser, Now())"
Else
DoCmd.Beep
MsgBox "Item has already been Shipped - Please hold for
Management", vbInformation, "ERROR"
End If
Set rst = Nothing
End If
Else
MsgBox ("Error - Incorrect Item Type Being Scanned")
End If

Now it will begin an If for Type2.... and then Type3... and so on...

Any way to stream line this?? ha!

thanks!
-gary
Click to expand...
Click to expand...
Click to expand...
 
Back
Top