Writing to a table using ado

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with combo boxes to choose course information and questions
associated with the course. My code uses ADO to write the combo box values to
my table, but not how I expected it. For one form, I want to generate
multiple records with one field for info values and another field for
question values.

intSerialNumber intItemID
c1 q1
c1 q2
c1 q3

Right now, with the following code, I make one record with fields for each
combo box and I am not sure how to get where I need to be...

If Not rstHistory.BOF And Not rstHistory.EOF Then
rstHistory!intSerialNumber = Me.cboSerialNumber

Dim intCounter As Integer
For intCounter = 1 to 3
rstHistory!intItemID = Me("cboItem" & intCounter)
Next intCounter

generates one record like this...
c1 q1 q2 q3

Can anyone help get me going in the right direction?
Thanks,
dc
 
Hi,


For intCounter = 1 to 3
rstHistory!intItemID = Me("cboItem" & intCounter)
Next intCounter



write 3 times in the same field (so only the last writing will be
remembered).


rstHistory.Fields("q" & intCounter) = Me("cboItem" & intCounter)



will try to write in 3 different fields (q1, q2 and q3).


Was it the problem?



Vanderghast, Access MVP
 
Yes, this is my problem. After additional contemplation, I revised to...

For intCounter = 1 to 3
rstHistory!intSerialNumber = Me.cboSerialNumber
rstHistory!intItemID = Me("cboItem" & intCounter)
If intCounter < 3 Then
rstHistory.AddNew
End If
Next intCounter

Which writes q1, q2, q3 sequentially to my intItemID field. Unfortunately,
it is writing 5 records. Before I added the second conditional, I was able to
write the 3 records, but it was generating an error because it was creating
an empty record on the last pass through the loop. I feel that I am close,
yet missing something rather simple.
 
Hi,

It is unusual to use AddNew in the end of the process... It is generally
used at the start of the process, as in the sequence:


rst.AddNew
rst.FieldName = ...
rst.OtherFieldName = ...
rst.Update



Hoping it may help,
Vanderghast, Access MVP
 
That did the trick. My AddNew at the end was a misguided attempt to solving
another problem.

Thanks for the help.
 
Back
Top