MS Access Form Question

[Shades1963]

Hello,

First time posting here so hopefully bear with me.

I am trying to create a simple database and form. I have a table named Accounts that has 452,000 records in it with three columns named Player ID, First Name, Last Name. I am trying to use a form to swipe a card with an account number encoded on it. Once the card is swiped I would like Access then find the account number in the Accounts table Player ID column and then add the Player ID, First Name, and Last Name into a new table named Adata. I also want it so that I can't have a duplicate account number/player id added into the Adata table.

I am trying to use a form through a switchboard so that not just anyone can get to the tables in the database.

I am not much of a programmer so I have struggled with this. I have tried adding a textbox and then bounding it to a macro and even an expression but nothing has seemed to work yet.

Any help would be much appreciated!

Shades

 

[TheAceMan1]

How are ya Shades1963 . . .

Using a form bound to the table, add an unbound textbox to the forms header. This is for receiving the swipped account number. Then using the On Got Focus event of the unbound textbox, copy/paste the following:

[COLOR=#204A87]   Dim Cri As String
   
   Cri = "[PlayerID] = " & Me.PlayerID
   Me.Recordset.FindFirst Cri[/color]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

Shades,
How far have you gotten?
Do you have a form with a text box for the swiped value?
Do you have a method/solution for getting the swiped value into the text box?
Are all of the Player IDs unique or are there duplicates?
Is there a reason you need to store all of the data fields in another table? Typically, this is bad practice.


Duane
Hook'D on Access
MS Access MVP

 

[Shades1963]

TheAceMan1 I appreciate your response.

dhookom, I've tried creating the form through the wizard and from scratch. I've bound the form to the Accounts table as TheAceMan1 suggested and added his code into the On GetFocus of the unbound textbox. When I swiped the encoded card it gave me the card number but nothing else. I know I'm missing steps but I'm still unsure what I'm doing. I've tried umpteen different combinations of macros and expressions to no avail.

I have one database now that does partially what I want. You can swipe a card into it and it will advance to next field where you can pick a number from a drop down box and when you press enter the boxes will clear and the account and corresponding number are automatically entered into the adata table. I am trying to take it one step further by adding the First Name and Last Name fields associated with those numbers. If I could just edit that form and add the two fields needed that would be fine too.

Each Player ID is unique there are no duplicates and we don't want to double swipe an account into the new table. The last form I mentioned is set to not let a duplicate account be entered. This data will be exported or copied out into an Excel spreadsheet to be filed away.

Thanks for both of your help so far.

Shades1963

 

[dhookom]

I think you would want the swipe to enter the code and in the lost focus, have it run some code. First add code to pop up a message box in the lost focus to see if it is triggered.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Shades1963 . . .

dhookom is correct. Try the lost focus event ... although I believe the After Update event would be ideal for this.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MacroScope]

Seems pretty straightforward to me. You should be able to do it in less than ten steps in a macro.

Once the value is entered into an unbound control on the user form, you should be able to fire its AfterUpdate event with a Tab key press (or Enter).

The macro conditions line contains an IsNull(DLookup()) expression which examines the Adata table to see if the record already exists. If it does, then no action takes place. Only if it does not exist (the DLookup is Null) will the events run. This prevents duplicate records. If a message box is desired to inform the operator that the name is already in the system it can be triggered with a Not IsNull(DLookup()) of the same expression. If the name is there it runs the operation, if not it pops a message.

Start with the Echo off action (so nothing shows on screen till everything is done), then OpenTable (Adata), GoToRecord (new), GoToControl (PlayerID), SetValue of Screen.ActiveControl to your PlayerID data from the card swipe by using a Form!Control reference. Then use GoToControl to go to the next control, FirstName, then SetValue Screen.ActiveControl again and use a DLookup to find the first name in the main table that relates to the PlayerID already in your form field. Use the GoToControl action to take you to the LastName field, and use another DLookup statement with a SetValue action to pull the last name from the main table and insert it into the appropriate control.

Last step is Close the table and you're done.