Create 2nd table based on criteria from a form?

[lin]

New to access...I have a large database containing employee name, ssn, address, phone number and photo. I have created a form which allows you to run a query on the ssn field and search the 1st database for the matching record. I want to then have this record copied to another table. I can get the query to display the correct record, however, it will not copy this info to the new table. Any suggestions?

 

[kathryn]

OK, I have to ask, WHY do you want to copy this record to another table? Generally this is not a good practice. There are exceptions, of course.

The easiest way to do what you want is to use an append query. You could design an append query to do what you want, and have the SSN as a parameter in the query. Your code could run the query, the query would ask for the SSN and append the appropriate record.

 

[Amiel]

"Insert Into [TheOtherTable]
Select * from [SourceTable] where [SourceTable].[ID]="
& Me![ID]

.. uses the unique index from the form (Me![ID])to select a single record from source table and stuff (copy) it into destination table.

you might attach it to you button with this...

DoCmd.SetWarnings False 'hide dialogs from user

DoCmd.runSQL "Insert Into [TheOtherTable]
Select * from [SourceTable] where [SourceTable].[ID]="
& Me![ID]

DoCmd.SetWarnings True 'restore dialog display.

However it might be simpler to add another field to the existing table, Say a yes/no field, and toggle this value rather than copy the entire record, with;

DoCmd.runSQL "Update [SourceTable] set [SourceTable].[SelectedYesNo] = -1 where [SourceTable].[ID]=" & Me![ID]
... sets the value to 'Yes' in the table.

 

[lin]

Kathryn,

I'm verifying usage of a recreation center by authorized personnel. Their info (full name, ss#, id card #, photo, etc.) is in the main table and when they come to the front desk I want to have their record appear when their ss# is scanned and then if they're in the table, have their record copied to another table so I can run daily reports on the number of visits.

That's why I want to copy the info. If they're not authorized (I have a field that shows active / inactive status) then they shouldn't be allowed admittance.

Have any suggestions on how I can write this better, PLEASE let me know. I'm pulling my hair out with this way.

Thanks!

 

[kathryn]

I would only copy the person's social security number and the date (and time if you want to count multiple visits on the same day). You don't need to copy anything else, because you have the data in another table.

Create a table called tblVisit with three fields:

ID (PK , autonumber)
SSN
DateTimeStamp

That should be enough data for what you described, I think.

 

[Amiel]

Lin, Create a second table (if you havent already) with the following fields:

[red]1.[/red] ID (autonumber) ' just a unique identifier for record.
[red]2.[/red] MemberID (number) ' this field will contain the unique ID from the members table[red]
3.[/red] EDate (date) 'will hold the date of the visit.
[red]4.[/red] Notes (String) ' Seems like a good idea. You be the judge of this.

The idea is to add a new record to this table when a member visits. So using a button on the form to execute the code, add something like the following:

Private Sub Button1_Click()

DoCmd.SetWarnings False
DoCmd.runSQL [red]"Insert Into [tblMemberVisits] (MemberID,Edate, Notes) Values (" & Me![ID] & ","& Now() & ",""" & Me![txtComments] &""&quot" [/red]
DoCmd.SetWarnings True

End Sub

Drop code (like this) on your form; when the user clicks 'Button1', a new record will be added to the Member Visits for the visitor record that is displayed on the form.

Note : This assumes that you have a Unique Key (called 'ID' here)for the Member table, if you don't create it first.

With code like this in place you may want to refresh the display to show the new record. If you choose to display the Visits (history) table in a list box (created with the wizard for instance) along with the Members record. You will need to refresh the list box after adding a record.

Finally. You can extract records for visitors with something like :

"Select * from Members, tblMemberVisits Where Members.ID = tblMemberVisits.MemberID and Members.ID = [Enter Member ID]"

There's a lot of stuff to digest here. Use it to get what you're after. Any further questions? Let us know.


[sig]<p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br> [/sig]