1
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(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"
'ADD CODE HERE TO PROCESS COUNT
'ADD CODE TO SET ID
Set QD2 = db.CreateQueryDef(Select Max(ID) from [ID Log])
'ADD CODE TO PROCESS MAX + 1
Set rsC = db.OpenRecordset("ID Log")
rsC.AddNew
rsC("ID") = Max+1
rsC.Update
rsC.Close
'ADD CODE TO ACCESS CORRECT QUEUE ENTRY
Set rsA = db.OpenRecordset("Call Queue")
Set rsB = db.OpenRecordset("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("Suggestions") = 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
