470,575 Members | 1,218 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,575 developers. It's quick & easy.

Moving Data from one RS to another RS

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.

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.

-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.


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(FirstNameT) Then
MsgBox "Please Enter First Name", vbCritical, "Invalid First Name"
Exit Sub
End If

If IsNull(LastNameT) 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.Delete ("Move_Queue_Query")
On Error GoTo 0

Set QD = db.CreateQueryDef("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_Query"


Set QD2 = db.CreateQueryDef(Select Max(ID) from [ID Log])
Set rsC = db.OpenRecordset("ID Log")
rsC("ID") = Max+1

Set rsA = db.OpenRecordset("Call Queue")
Set rsB = db.OpenRecordset("Call Log")
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("Suggestions") = SuggestT


Set rsA = Nothing
Set rsB = Nothing
Set rsC = Nothing
Set db = Nothing

MsgBox "Added " + FirstNameT + " " + LastNameT, , "Success"
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
Dec 28 '06 #1
1 1807
32,295 Expert Mod 16PB
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
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblStudent
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. MaxMark; Numeric
  7. MinMark; Numeric
Dec 30 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Stephen Ghelerter | last post: by
3 posts views Thread by genojoe | last post: by
1 post views Thread by =?Utf-8?B?UmljaA==?= | last post: by
15 posts views Thread by mcjason | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.