Form to update records within a table (1 Viewer)

[Johnsonsimon]

Hi again all,

Still working on the registration and badge print system for their upcoming exhbition the company I do a bit of work for. Managed to solve most of my problems but have got stuck here, just cant figure my head around it.

Basically each of the badges have a barcode (code 39) on them which is in the following format...
*1000000123$M*
(*'s required for the barcode to work, the numbers are the badge number and the $M is the enter command (i think))

Basically we need a form that allows us to scan the barcode (the number of the barcode is the Primary Key in the tbl_Attendees), and for this to update the "Attended" (Yes/No) field to yes. This bit I can do with a simple update query, but we need some form of confirmation, something simple like a line of text showing up on the form saying

"Joe Bloggs of Leeds registered as Attended"

I am sure I have done something similar in the past but for the life of me can't remember how.

 

[Lightwave]

Go to the form in question.

Hit designview for that form and within the tools button select the label button and place a label on the form approximately where you want your message to appear. Access increments label and calls them label1 , label2 etc.. Remember what you call the label and ensure you place some text in it I would suggest "Attendance unconfimed"

Now select the yes no attended field and go to properties and scan down to the BeforeUpdate event enter the vb code window and place something.

If Me.Attended = True Then

         Me.LabelName.Caption = "Attendance CONFIRMED"

Else

         Me.LabelName.Caption = "Attendance unconfirmed"

End If

LabelName will be the same name that access gave the field you created in the first step...

Save go to a record and flick between confirme and unconfirmed. Changing the format properties of the field will alter properties of the text. You can if you want get creative with the properties and programmatically change them placing them in the above code.

If you want you can dynamically alter the label with information from the table, in this case you mention the atendees name.

For instance you could try.

Me.LabelName.Caption = [Attendee] & " Attendance CONFIRMED"

Where [Attendee] is the name of the field on the from which contains the attendee name - unlikely to be attendee in your database.

 

[Johnsonsimon]

Hi Lightwave

Thanks for the reply.

The form in questions "frm_ScanAttendees" only has a text box on it with an OnEnter event that runs the Update Query.

So there is nothing there for me to run Me.Attended against, as that data is just contained in a table. What i need to be able to do is lookup the number that has been scanned, after it has been scanned and the update query has run, against the tbl_Attendees and then show the result. I guess something similar to a VLOOKUP in excel, where you can display the result of say column 5 after the lookedup value.

I just cant get my head round how to do this lol

 

[Lightwave]

Ok I see what you mean.

I think you need some variation of a Domain Function.

Particularly DLookup

Have a look at this and see what you think

Access World Forum's very own pr2-eugin blog on Domain Functions

You will need to pass the number that has just been scanned into a variable and then pass that to a domain function which has something to return the value to.

You can then pass the returned yes / no value to the label on the form..