Moving Data from one RS to another RS

[maiami2]

Hi Guys,

I'm very new to access and I've been trying to get the hang of it over the last few days, but I've become very confused with various aspects of how to code it.

Introduction:
I am using 3 tables for my data base at the moment. The first table is an ID table, which tracks IDs that are in use/taken. The second table is a queue, which has very little/limited information on a person. The third table is a log, which has much more data on a person. The user enters in the first name, last name, and telephone number and the data gets transferred from the queue table to the log table.


Goals:
-Move data from queue to log(make new entry in log).
-Assign new log entry an ID number, add ID entry to ID table.
-Delete entry from queue where the data had been taken from.



Code:

Private Sub MoveEntry_Click ()
Dim db As Database
Dim QD As QueryDef
Dim QD2 As QueryDef
Dim rsA As DAO.Recordset
Dim rsB As DAO.Recordset

'The 3 Primary Key Values for Both Tables that should be entered into the form

If IsNull(FirstNam eT) Then
MsgBox "Please Enter First Name", vbCritical, "Invalid First Name"
Exit Sub
End If

If IsNull(LastName T) Then
MsgBox "Please Enter Last Name", vbCritical, "Invalid Last Name"
Exit Sub
End If

If IsNull(PhoneT) Then
MsgBox "Please Enter Phone Number", vbCritical, "Invalid Phone Number"
Exit Sub
End If

Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.De lete ("Move_Queue_Qu ery")
On Error GoTo 0

Set QD = db.CreateQueryD ef("Move_Queue_ Query", _
"Select Count(*) from [Call Queue] where [Last Name] = Me![LastNameT] AND [First Name] = Me![FirstNameT] AND [Phone Number] = Me![PhoneT];")
DoCmd.OpenQuery "Move_Queue_Que ry"

'ADD CODE HERE TO PROCESS COUNT

'ADD CODE TO SET ID
Set QD2 = db.CreateQueryD ef(Select Max(ID) from [ID Log])
'ADD CODE TO PROCESS MAX + 1
Set rsC = db.OpenRecordse t("ID Log")
rsC.AddNew
rsC("ID") = Max+1
rsC.Update
rsC.Close

'ADD CODE TO ACCESS CORRECT QUEUE ENTRY
Set rsA = db.OpenRecordse t("Call Queue")
Set rsB = db.OpenRecordse t("Call Log")
rsB.AddNew
rsB("ID") = ID
rsB("Date") = rsA("Date")
rsB("Time") = rsA("Time")
rsB("Last Name") = rsA("Last Name")
rsB("First Name") = rsA("First Name")
rsB("Phone Number") = rsA("Phone Number")
rsB("Notes") = rsA(Comments) + ", " + NotesT
rsB("Suggestion s") = SuggestT
rsB.Update
rsB.Close

'ADD CODE TO DELETE RECORD FROM rsA

rsA.Close
Set rsA = Nothing
Set rsB = Nothing
Set rsC = Nothing
Set db = Nothing

MsgBox "Added " + FirstNameT + " " + LastNameT, , "Success"
DoCmd.Close
End Sub



Known Problems:

So far, I do not know if I'm using QueryDefs right at all. But I have two in there.

The first one does a count to check if there exists an entry in the queue table that matches the first name, last name, telephone number. If this is a good way to do it so far, I need help on how to process the count. Is the querydef another table? Do I just grab the value? Or is there a way to store the count into a variable somewhere so I can just do a comparison statement like count ==1?

The second querydef does a max on the ID table to find out what the maximum value is. Similar to the first question, is there a way to store tha max into a value and then just add 1 to it and set that as the new ID number?

Next, accessing the correct record in the queue log to transfer information. Do I do that the same way I did the other querydefs? Is there a more effecient way to do it?

Lastly, I would like to understand how to delete the record I assessed from the queue table.



Thank you for reading this long problem. Any help would be greatly appreciated. I apoligize if some of these problems are routine and simple, I've tried looking on google but maybe I'm not typing in the right keywords

[NeoPa]

Let's forget this long complicated question and build up from scratch.
Precision and accuracy are very important in communicating remotely.
Q1. Do you have a form that an operator enters detail into which then populates the Queue table?
If not, please respecify the basic requirements without the necessity of working it out from your code.
We will need shortly, a post with your MetaData laid out.
Here is an example of how to post table MetaData :
Posting Table/Dataset MetaData

Table Name=tblStudent
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
MinMark; Numeric