Auto change a value when a related value is entered in another field

[NewGuy]

Hello,


Here is the situation... I have an event across multiple sites where we have hundreds of accredited members. For the members I have the following information in table "People":

• AccredID - a unique member identification number (PrimaryKey)
• FirstName
• LastName
• e-mail
• Language
• Site

I also have wireless headphones that enable simultaneous translation. For the headphones, I have the following info in table "Headphones":

• Serial - unique to each set of headphones (Primary Key)
• Status - a list of selections (Available, Signed Out, Damaged, Lost)

I have these set up in a simple query People left joined with Headphones. I have a form set up to speed up the headphone checkout process. My intention is to be able to scan the AccredID into the relevant field on the form then scan the serial number into the relevant field on the form. Is there a way for me to program the form such that when the Serial is entered, the form auto-changes the status of the device to "signed out"?

Any thanks would be greatly appreciated - I've been trying to solve the problem for hours and can't see straight any more!

NewGuy

[Dal Jeanis]

Those two tables aren't related to each other. The table you are missing is WHO checked out WHICH set of headphones.

If you aren't tracking that, then all you need to do is have your form update the status of the headphone. The user id has no relationship to the update.

Put a command button cmdSignOut on the form, and assuming the Serial is scanned into a textbox called txtMySerial.

Private Sub cmdSignOut_Click()

Dim strSQL As string
StrSQL = "UPDATE [Headphones] SET [Status] = 'Signed out' WHERE [Serial] = " & Me.txtMySerial
CurrentDB.Execute strSQL

End Sub

The above is aircode, but it should get you there.

If you DO want to track who checked out which serial, then create that checkout table, use code similar to the above to do an insert to that table with the checkout date/time, then use the above code to update the headphone status. When headphones arrive back at the station, you'd update the status and mark the checkout record with the return date/time.

[NewGuy]

Those two tables aren't related to each other. The table you are missing is WHO checked out WHICH set of headphones.

Apologies, in an effort to simplify the question I neglected to mention that I do indeed have a table that brings together who checked out which headphones along with a whole host of other registration info that isn't relevant to the question at hand!

Thanks for your reply, it looks like exactly what I need!

[NewGuy]

Thanks again for your help, but I've run into another issue!

I used the code above, but now I'm getting a "Method or data member not found" error specific to the Me.txtMySerial part of the code. From what I know, this error happens when the command referenced doesn't exist on the form. I've double-checked and the Serial field is definitely part of the form...I even tried using just Me.txtSerial with the same result.

Thoughts?

[ipisors]

Please post all the code you're currently using, exactly as you're using it.

Are you sure there's not a Subform involved? The syntax can be somewhat more challenging if there's not a subform.

Otherwise, I'd say it means you don't have a control called txtMySerial

[NewGuy]

Ok, I've solved it. It was a simple as needing to use code snippet Me.Serial.value rather than Me.txtSerial.

Thanks for your help Dal Jeanis and ipisors!

[ipisors]

Ok - glad to hear it is working for you. We were glad to help.