Edit record in table ...

[tinymind]

The problem ...

I want to edit a record in a table depending what the user has selected from the form.

There are three combo boxes: Name, Date and Attendance

I need one button on the form that the user presses to update the attendance record that matches the criteria in the Name and Date fields.

How do I get Access to edit the record already in the table for that date?

Help ......

Tiny

 

[FoxProProgrammer]

Hi again, Tiny!

One way to do this is to Set a recordset, Find the record for that date, and edit the record. The only problem that I can see with this is if more than one record in the table have the same date. If that could happen, or if you want to design it with that in mind, I'd recommend that you add an Autonumber ID field to the table and make it the key field. Then you can Find that ID instead of the date and never have to worry about whether a date is unique. If this isn't an issue for you, let me know and I'll provide you some code to edit the record.

Best, dz
dzaccess@yahoo.com

 

[tinymind]

HI DZ ... new problem that maybe you can help with ..

I need to learn VBA ...

The detail ...

This form is to be set up so the user can only edit one column in the table.

I have three combo boxes Name, Date and Attendance on the form.

The name and date fields when contcatenated together create a primary key. With this key I want to edit the record in the next column .. the attendance row.

I know it is a simple Edit statement but ... you know ... theres never enough hours in the day to do all the tasks you wanna do ...

Tiny!

 

[FoxProProgrammer]

No problem, Tiny. I'd still recommend that you add an Autonumber key field to the table. It makes searches easier, especially when a unique record is identified by the combination of a text field and date field. Here's some code if you want to do it that way.

Assumptions for this example:

Table name: Attendance
Name field: Name
Date field: Date
Attendance field: Attendance

Form:
Name Text box: txtName
Date Text box: txtDate
Attendance Text box: txtAttendance

Place this code in the event that runs when you want to save the record, such as the OnClick event of a command button named "Save". To try the code, input the new Attendance data in the text box on the form and press the Save button.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "Select * From Attendance Where Name = " & Chr(34) & txtName.Value & Chr(34) & " And Date = #" & txtdate.Value & "#;"

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

If rs.RecordCount <> 0 Then
rs.Edit
rs!Attendance = txtAttendance.Value
rs.Update
Else
Msgbox &quot;No matching record found.&quot;
End If

rs.Close
db.Close

Hope this helps.

Just as a side note, you could do a rs.MoveLast and then look at the value of rs.RecordCount. If it is greater than 1 you have a problem with your data because it means that more than one record have the same name and date. dz
dzaccess@yahoo.com

 

[tinymind]

The above code returns the message ...

&quot;Too few parameters. Expected 2&quot;

I cant see what it is doing wrong!

The DB breaks after the ...

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

of the code!

Tiny

 

[FoxProProgrammer]

Hi Tiny,

This error indicates that Access doesn't recognize the two parameters in the Where clause. Are the names of the combo boxes txtName and txtDate? What does txtName contain? Is txtDate a valid Date? Actually I made a mistake and thought that these were text boxes, which is why I recommended that you name them txtName and txtDate. Since they are combo boxes, you might change the names to cboName and cboDate for clarity, but it will work with the existing names. How many columns are in the combo box? If there's more than one, make sure that the bound column contains the Name and Date instead of another field. You can also reference a specific column in a combo box with cboBox.Column(ColNum). Are the field names in the table Name and Date respectively? If you don't see anything obvious based on these questions, set a breakpoint in your VB at strSQL = ... look at the value of txtName, txtDate, and strSQL after the statement executes. I tested this code and know that it works if everything is set up properly.

Just curious...are you displaying all dates in the Date combo box, or only the dates that match the event selected in the first combo box? After you get the Edit working, we can come back to this. I'd probably set it up a little differently. Use a combo box for the Dates. When the user selects a Date, all the events for that date pop up in the Name combo box. When the user selects the Name that they want to edit, the attendance for that event shows up in the Attendance text box (not combo box). You could then have another text box where they enter the new attendance. I would also make the Name combo box invisible until the user selects a date to make sure that they do it in the right order. You could also make the attendance text box invisible until they select a name. Alternatively, you could present the name combo box first, but then it seems like you only need text boxes for the date and attendance.

Let me know how it's going if you get a chance.

Best regards, dz
dzaccess@yahoo.com

 

[tinymind]

I wondered if you could do that [cboBox.Column(ColNum)] ... Now I know you can ..

Resolved the issue ... the Format on the text box was set to text and not date and therefore Access could not understand the sql statement ...

Thanks for all your help .... DZ, ... it has been well worth it ...

Now I have to populate the app and test it to see if they can break it ... this is the fun bit ...

Tiny

 

[FoxProProgrammer]

Hey, Tiny:

I'm glad that it worked out. Here's another suggestion that you might want to consider. If I understand the purpose of the form in quesion, you want the user to select an event so they can change the attendance number. If so, instead of presenting the Date, Name, and attendance in three combo boxes, you could present all three fields in a single three-column combo box. The form could also have a text box where the user enters the new attendance, and a button to save the change. It seems to me that this is a little more user friendly because the user can see all the pertinent data at one time, rather than cascading combo boxes. If you want to try it like this and need help with the code let me know.

Best,
dz
dzaccess@yahoo.com