AddRecord issue - Visual Basic with Access

[technocraze]

Hi guys,

i tried using the AddRecord command (Record operation - Add New Record) provided by MS Acess but the new record is not been appended to the table but instead it has been overwritten.

I have set the focus for the field under the acNewRec already. By right i shld be able to append the record at the end of the record of the field and nt overwritting the record.

What i want to achieve is to be able to select the value from the combo box corresponding to the table 'Student' and append the record based on what have been selected from the combo box (Predefined list) and the remaining textbox values or is there a possibility to use RecordSet properties to append the record? I do knw about adding record using rs.AddNew.

Any code snippets, guidance or correction of my code or logic is very much appreciated. I have just started to embark on VB nt long ago. Hence, considered a novice learner.

Implementation logic

primary key
SerialCode (Autokey)

Fields
StudentId, subjectcode, course, grade

Combo box
StudentId (predefined list) - S1, S2, S3, S4, S4, S5

AddRecord command (by Acess under operation)
acNewRc
StudentId.SetFocus
subjectCode.SetFocus
course.SetFocus
grade.SetFocus

code

Private Sub AddRecord_Click()
On Error GoTo AddRecord_Click
DoCmd.OpenForm "Record"
DoCmd.GoToRecord, "Record", acNewRec
Combo1.SetFocus 'predefined list
StudentId.SetFocus 'bound to controlsource studentId
course.SetFocus 'bound to controlsource course
subjectCode.SetFocus
Grade.SetFocus
Exit_AddRecord_Click:
Exit Sub
Err_AddRecord_Click:
MsgBox Err.Description
Resume Exit_AddRecord_Click
End Sub

Private Sub Combo1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[SerialCode] = " & Str(Nz(Me![Combo1], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Tks for your assistance.

[MSeda]

Delete the combo1 after update, it is changing moving to the existing student record. Try opening your form like this:

 DoCmd.OpenForm "Record" , , , ,acFormAdd

This will open your form in data entry mode already on a new record. You can use the DoCmd.GoToRecord, "Record", acNewRec to add additional records.

Also I don’t really understand all of the set focus business you have, Setfocus just moves focus to a control it does’nt do anything to the control, post back and tell us what you are trying to do here. I think you probably don’t need any of the setfocus.

In short, as far as I can tell all you really need is:

Private Sub AddRecord_Click()
On Error GoTo AddRecord_Click
 
DoCmd.OpenForm "Record" , , , ,acFormAdd
 
Exit_AddRecord_Click:
Exit Sub
Err_AddRecord_Click:
MsgBox Err.Description
Resume Exit_AddRecord_Click
End Sub

[technocraze]

Hi dude,

I cannot call out acFormAdd. btw, what u meant by ....
what i want is to append the record to the table. criteria (selected value from the combo box). Remove duplicate StudentId entry.

Delete the combo1 after update, it is changing moving to the existing student record. Try opening your form like this:

 DoCmd.OpenForm "Record" , , , ,acFormAdd

This will open your form in data entry mode already on a new record. You can use the DoCmd.GoToRecord, "Record", acNewRec to add additional records.

Also I don’t really understand all of the set focus business you have, Setfocus just moves focus to a control it does’nt do anything to the control, post back and tell us what you are trying to do here. I think you probably don’t need any of the setfocus.

In short, as far as I can tell all you really need is:

Private Sub AddRecord_Click()
On Error GoTo AddRecord_Click
 
DoCmd.OpenForm "Record" , , , ,acFormAdd
 
Exit_AddRecord_Click:
Exit Sub
Err_AddRecord_Click:
MsgBox Err.Description
Resume Exit_AddRecord_Click
End Sub

[MSeda]

Your going to have to explain what your trying to do more clearly.
You may have some of you terminology confused, append means to add a new record.
Update means to make changes to an existing record.

You say you want to remove the duplicate studentID entry. Does this mean that there should be only one record in the table for each student (no Duplicates) If a record already exists for a selected student do you just want to change the data in that record and not add (append, goto acnewrecord..etc.) a new record?

The code you posted opens the form Report and Goes to a new record, then moves focus around the form without doing anything
Then in the combo after update you move to an existing record based on the selection In the combobox.
Going to a new record when you open the for and then having a combobox move to another record doesn’t make sense.

[technocraze]

Yes, i dnt want the existing StudentId to appear in the table, that means to filter duplicate. I have set this Yes(No duplicate) undet the design view but it give an error - This will allows duplicate entries in tha table. There are two more issue I am encountering, It would be very appreciated if you can take a look at it.

Title : RecordSet update insert error & sql insert syntax error
Title: Listbox values nt shown in textbox - AfterUpdate event

tks

[MSeda]

Sorry for leaving you hanging so long, but let me ask for a little more clarification.
This is what I understand about your form and tables.

Your form is bound to a table (I’ll refer it as ‘Course Table’) with the fields Fields:
StudentId, subjectcode, course, grade.
A combobox on your form gets its rowsource from ‘Student Table’ to fill in the StudentID field in ‘Course Table’.

Like I said giving the command ‘GoToNew’ after opening the form and then having a combobox that returns to an existing record doesn’t make sense to me, but I’m not sure what to tell you because I am still not clear as to what you are trying to do.


When you say

i dnt want the existing StudentId to appear in the table, that means to filter duplicate.

Do you mean that you want this Combobox to only list StudentIDs that do NOT already have an entry in ‘Course Table’ ? (This would mean each student ID could only be associated with one course.)
Could you describe exactly what information 'Course Table' Stores it seems to me that you would want multiple entries for each student in order to list all of the courses they are taking.

Also do you want the form to Find existing records for the Student Selected and edit them or do you want to create new record for the Student Selected?

In a previous post you said:

what i want is to append the record to the table. criteria (selected value from the combo box). Remove duplicate StudentId entry.

This sounds to me like you want to add a new record for a student and at the same time delete any previous so there is only one record. Again doesn't make much sense to me but maybe I'm not understanding what exactly you want your form to do or what type of data your table is storing.

Please post back, this time it shouldn't take me four days to reply. or maybe someelese can offer a better insight.