Macro which will add a record to the table

[Matizo]

Hello,

What I need is command button which will add a record to one of my tables. The problem is that first what i have to do is select a student in the list box and then use a button which will add a rcord to student attendace table for that selected student. It should also be possible to add multiple records (if all students are selected).

How to create a macro/ or what is a code for it?


Thanks in advance,

matt

 

[gemma-the-husky]

in code on the onclick event of the buutton, you need something like the following

dim rst as recordset

'note that rst is the recordset of the ATTENDANCE TABLE not the student table

set rst = currentdb.openrecordset("attendancetable")
rst.addnew 'this adds the new record to the table
rst!studentid = [whatever the id is in the list box]
rst!attendancedate = [however you determine]
rst!anyotherfield = [whatever you want to do]
-------- etc
-------- when finished
rst.update 'if all is well this is ok - if you have any key issues eg duplicate keys, or missing date in your record the update will fail, so you need some sort of error handling in this code
rst.close

----------------------------
now, if you have multiple selected items in the list box then you need to add some code to iterate the items

which is something like
dim itm as variant
for each itm in listbox.selecteditems

'and inside this put the rst stuff again
'you need to dereference the itm to determine the details of the list box row, and i am not 100% sure of the syntax for this, offhand

next itm

--------------------
this is starting to get complex, so its best to get it working for 1 item first, then look at iterating the listbox afterwards.

I don't know whether you can do this with macros, but in general code is far better to use than macros.

 

[Matizo]

thanks! that was really helpful!

This is code that is used:

Option Compare Database

Private Sub cmdAddAtt_Click()
On Error GoTo Err_cmdAddAtt_Click

Me.txtNotice = AddAttendanceRecord(Me.lstStudents)
Me.lstStudents.Requery

Exit_cmdAddAtt_Click:
Exit Sub

Err_cmdAddAtt_Click:
MsgBox Err.Number & "-" & Err.Description
Resume Exit_cmdAddAtt_Click

End Sub

Public Function AddAttendanceRecord(ctlRef As ListBox) As String
On Error GoTo Err_AddAttendanceRecord_Click

Dim i As Variant
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef

Set dbs = CurrentDb
Set qd = dbs.QueryDefs!qAttendance
Set rst = qd.OpenRecordset

For Each i In ctlRef.ItemsSelected
rst.AddNew
rst!StudentID = ctlRef.ItemData(i)
rst!AttendanceDate = Me.txtToday
rst.Update
Next i
Set rst = Nothing
Set qd = Nothing
AddAttendanceRecord = "Records Created"

Exit_AddAttendanceRecord_Click:
Exit Function

Err_AddAttendanceRecord_Click:
Select Case Err.Number
Case 3022 'ignore duplicate keys
Resume Next
Case Else
MsgBox Err.Number & "-" & Err.Description
Resume Exit_AddAttendanceRecord_Click
End Select

End Function


But there is still something... I'm getting the error msg:

"4321-Data type conversion error"

I dont really know what that means. Is there a problem with the code or settings in tables or data?
I have attached the database.

Many thanks,
Regards

 

[gemma-the-husky]

this row is likely to be wrong

rst!StudentID = ctlRef.ItemData(i)

the itemdata is the whole row of the listbox - i think you probably need to try and select a particular column for your needs - something like

itemdata(i).columns(0) for the first column
itemdata(i).columns(1) for the second column

note that the columns are the checked columns in the source query, NOT the visible screen columns.

To help, put a breakpoint in your code margin - just click in the margin, you should get a brown blob in the margin - click the blbo again to remove it when you are done with it. Your code will temporarily stop executing at the break point. you can then press F8 key to step through the code a line at a time, or press run either debug,run or the little right pointing arrowhead in the icon bar to continue normally - if you hover the mouse cursor over any variable you will see what it contains - it will also let you see EXACTLY where the code is failing. You can have multiple break points in your code. They are really useful for debugging

 

[Matizo]

Thanks for help! The method you told me is really usefull however that was not a problem with the code. The data type conversion error was appearing because there was an error in the qAttendance query.

This is SQL that I used first:

SELECT tblStudents.LastName & ", " & tblStudents.FirstName AS FullName, tblClassesTaken.StudentID, tblClassesTaken.ClassID
FROM tblStudents INNER JOIN tblClassesTaken ON tblStudents.StudentID = tblClassesTaken.StudentID
WHERE (((tblClassesTaken.ClassID)=[Enter CLass ID: ]))
ORDER BY tblStudents.LastName & ", " & tblStudents.FirstName;

And when I changed it for this one:

SELECT tblStudents.StudentID, tblStudents.LastName & ", " & tblStudents.FirstName AS FullName
FROM tblStudents
ORDER BY tblStudents.LastName & ", " & tblStudents.FirstName;

The database works However I still want to enter the class number first before recording the attendance. It should not be too difficult to change the first SQL code so it would workd.

regards.
matt

 

[Therese]

Okay - I have searched, but can't quite find the answer. Please direct me to a post if this has been answered a million times. I have used the above code for adding multiple new records. When I used "gemma-the-husky"'s code, I got the records added, but unfortunately the wrong items selected appeared. I am using my dog kennel database and say for example the dogId for Rover is 42, but he happens to be the second one in the drop down list, then I am choosing Rover, but dogId 2 is showing up instead of 42. Can someone help with that problem.

When I use the code that Matizo provided. The records get added without the dog ID at all. So If I choose 7 dogs, 7 records are added with all the other info, but not the dogs that I selected. Can someone direct me to that problem. If needed, I can attach a modified version of the database. I was just hoping it was something obvious I was missing. Thanks in advance for any info.

Therese