Accidently scanning equipment twice.

[Hackcess2007]

I have a form with a combo box. I scan the barcode number into the combo box and it is entered into the "Equipment Out" report. My problem is if there is a lot of equipment be check OUT, the end user can accidentally scan a piece of equipment out a second time. I have run duplicate querys and what not but it seems like it would be so much better to add a macro or code to only allow one instance of the equipment barcode. As you may know I am not very good with vba but I will try. Any help is much appreciated.
Thank you.

 

[spikepl]

Now be a good guy and delet your otehr two posts on the same subject. Triple posting gets you in the dog house pronto. So does double posting.

 

[CJ_London]

Thanks Spike, duplicate post here

http://www.access-programmers.co.uk/forums/showthread.php?t=277250

I have deleted the other posts

 

[Hackcess2007]

OK thanks guys. I am not sure if different people would be only into curtain topics, and I felt my question had a bit of overlap. but not a problem. i very much appreciable this resource.

 

[Hackcess2007]

CJ London,
Yes after reading the link I get it. Much better to follow the flow of a thread than wasting other peoples time if they are also spending time trying to help. Is kinda when going on a new job interview and they hand you a packet and say come back with your best answer to what's inside this packet on your followup interview....and all the while they are reading every applicants answers only to find a solution for their existing business situations. Well...maybe not like that!
Anyways thanks for the advice on my question.

 

[Hackcess2007]

Good morning, I am hopefully able to start out on fresh foot.
First attachment is my transaction tbl. The CrewChiefName is the auto number from the EmployeeTbl. The BarCodeNumber is the auto number from the ImportEquipTbl. The TransactionDate and the EquipOutIn are set from the transaction Forms. Please see the attachment of the transaction form. In the Check Out equip form the cursor is in the BarCodeNumber combobox and is the only object with a tab stop so the scanner enters the barcode number and is already there for the next scan. The static OUT is set in the Default Value "OUT". The static Crew chief name is also set with a Default value employees auto number. the name is from a lookup in the combo box. Because of my lack of vba i have made a :EquipOUT form for each employee and a EquipIN form for each employee setting the default values on each for what is needed. I cannot let the end user populate a dropdown box to pick a name or what the transaction type is. It had to to be as user friendly as possible. We have many different people that use this during the day. I will be happy to add any other information. Thanks very much for any help.

Anyway If i can add some type of code in the after update event that will look into the transaction table and see the statics of the equipments last scan and if it was set to "OUT" and the end user is trying to scan it out again. Our just the opposit in the check 'IN" form. I have set up Find duplicats qrys BUT that recalls all transaction of the piece of equipment and not the last two. I this is add much more respectability to learn how to keep duplicate entries from being scanned in the first place.

 

[CJ_London]

If i can add some type of code in the after update event that will look into the transaction table and see the statics of the equipments last scan and if it was set to "OUT" and the end user is trying to scan it out again. Our just the opposit in the check 'IN" form

So, even with your multiple threads, you don't read them. This was answered on the link referred to here

http://www.access-programmers.co.uk/...d.php?t=277250

If it is not an answer that works for you, please explain why not

 

[Hackcess2007]

CJ London,
I looked this morning for about 10 minutes and could not find it. I read it thou last night and even tried but I know that I most likely had it incorrect.

 

[CJ_London]

I see the thread has been closed - so repeated here. Note the fact you have separate forms for in and out does not matter other than you would need to repeat for each form. For future reference, if you click on UserCP (top left of this page) then 'list subscriptions' you can see all your threads
_______________________________________________________
I'm sure it can be done, but you need to explain how your tables and forms work - provide some examples. scanning the barcode is one thing, determining if it has already been scanned for this 'event' is another since presumably the same barcode is scanned 'in' and 'out' on a regular basis - is it several times a day (e.g. bowling shoes) or daily (e.g. dumper truck)

What I would expect is something like after scanning, the system would check what the previous scan was - if scanning 'in' and the previous scan was also an 'in' then reject, otherwise OK. Previous scan would probably be defined by date and/or time so something like this code in your combo afterupdate event would work

dim rst as dao.recordset
 
set rst=currentdb.openrecordset("SELECT TOP 1 Direction FROM tblBooking WHERE EquipmentCode='" & me.combo & "' ORDER BY BookDate Desc")
if rst!Direction="IN" then 'already booked in 
    msgbox "Already Booked In"
    me.combo=""
end if
set rst=nothing

This also assumes you have one 'booking' table to record both in and out transactions

 

[Hackcess2007]

Cj London,
I made some changes on the code. Could you take a look and see if i got it.
dim rst as dao.recordset

set rst=currentdb.openrecordset("SELECT TOP 1 Direction FROM TransT WHERE EquipOutIn='" & me.combo & "' ORDER BY TransactioDate Desc")
if rst!Direction="IN" then 'already booked in
msgbox "Already Booked In"
me.combo=""
end if
set rst=nothing

Last night I tried working the code but was not very successful. So to get
a little closer,
When doing the me.combo, should i change that to me.BarcodeNumber?
And this would look like I would change tblBooking to TransT and EquipmentCode= to BarCodeNumber= and BookDate to TransactionDate.

 

[CJ_London]

Since I didn't know the names of what you are calling your fields and controls, I used 'pseudo' names.

So what you suggest sounds right.

I note you appear to have a typo here - ORDER BY TransactioDate Desc

don't forget to change this
rst!Direction

and I assumed you would have text values In/Out, if not, then don't forget to change this as well

if rst!Direction="IN" then

 

[Hackcess2007]

Wow I will give its go !! Thanks

 

[Hackcess2007]

Hi CJ London,
I have been working on this today and I received a run time error 3061" too few parameters expected 1. I have looked into the error, i see one said that could be spelling and I went thru and rechecked everything but nothing jumped out and bit my nose. Also something about form and a sub forms. Would you happen to see anything that stands out to you? One more thing everything from Set rst to Desc") is highlighted in yellow. Thank you

Private Sub BarcodeNumber_AfterUpdate()
Dim rst As dao.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT TOP 1 Direction FROM TransT WHERE EquipOutIn='" & Me.BarCodeNumber & "' ORDER BY TransactionDate Desc")


If rst!Direction = "IN" Then 'already booked in
MsgBox "Already Scanned This Piece"
Me.BarCodeNumber = ""
End If

Set rst = Nothing



End Sub

 

[CJ_London]

well going back to some of your original info, it looks like

'Direction' is actually 'statics'
'EquipOutIn' is actually 'barcode'
TransactionDate is actually [Transaction Date]

 

[Hackcess2007]

Hi CJ London. things not going so well. I thought i may reintroduce some vital information to this riddle.
the form name is Alexis_IN
the table name is TransT
the date field is TransactionDate
the statics field is EquipOutIn
the bar code field is BarcodeNumber
I appreciate all the help but I don't want to be a pain. If this is the kind of thing that would require extensive time, I should probably consider paying someone to do. Let me know if that is the case.
Thanks

 

[CJ_London]

this should not be difficult, the syntax is correct, it is just a case of getting the names right. There are two names to consider, the name of the field in your table and the name of the control on your form to which it is bound (i.e. the field name that appears in the controlsource)-

Unfortunately, for example 'the statics field is EquipOutIn' is not clear what you mean - are you saying the name of the field in your table is 'statics' and the name of the control to which it is bound is called 'EquipInOut'?

So to restate

Set rst = CurrentDb.OpenRecordset("SELECT TOP 1 [COLOR=red]EquipOutIn[/COLOR] FROM TransT WHERE [COLOR=red]BarcodeNumber[/COLOR] ='" & [COLOR=darkgreen]Me.BarCodeNumber[/COLOR] & "' ORDER BY [COLOR=red]TransactionDate[/COLOR] Desc")
If rst![COLOR=#ff0000]EquipOutIn[/COLOR] = "IN" Then 'already....

this is based on:
1.names in red are the names of the fields in your table
2.name in green is the name of the control on your form
3.me.BarCodeNumber is the name of the control your are scanning a value into

So for example if the name of the field in your table is 'statics' change EquipOutIn to statics in both places

 

[Hackcess2007]

Hi CJ London,
I deleted and re-entered the code you provided and I think my problem is that the combo-box on my form [BarCodeNumber] is a...lookup. I looked into the error 3464 Data type mismatch in criteria expression. and I think that my be the problem. Is there a addition to the code that would link the tbl BarCodeNumber to the Me.BarcodeNumber control on the form? The following is the row Source of the combo box we are putting the after update event into..SELECT ImportEquipTbl.EquipmentID, ImportEquipTbl.Barcode FROM ImportEquipTbl ORDER BY ImportEquipTbl.[Barcode]; The Rocord Source for the FORM is the TransT
Thanks again for your help, I hope there is an easy way to get around this!

 

[CJ_London]

Ah, so the rowsource for this lookup is say

SELECT Barcodenumber, BarcodeDescription FROM Products

if that is the case and assuming you are storing the uniqueID in your TranT table just remove the single quotes from here

....WHERE BarcodeNumber =" & Me.BarCodeNumber & " ORDER....

 

[Hackcess2007]

CJ London,
Please see attached file.

 

[Hackcess2007]

CJ London,
I can't thank you enough for sticking with me on this. I DID learn that vba extremely powerful and that you can basically do anything to your applications with it. I am going to get into more vba (on a entry level of course) but just seeing what types of things can be done has peaked my interest, and who knows may take my profession in a different direction.
Thanks again
Best regards !