Adding to a Table from a Form

[Crampton]

The problem:-

I have a form with a combo box with the values 1,2,3,4,5 in the drop down list.

I would like a user to select one of the numbers 1-5, lets say number 2. I would then like access to Add the corresponding number of records to a table ( in this case add 2 records )

Can this be done.

There is a field that links both the Form and the table together and that is Enquiry Ref.

Many thanks for any help

 

[Mile-O]

It can be done with DAO or ADO.

 

[Crampton]

Thanks Mile-o-phile.

I looked up Access help on the subjects of DAO etc and found them to be a bit confusing or not quite what i am after.

If my Form is called "frmA' and the field within this Form is named "FieldA" ( it is this field that has the combo box 1-5 ) and the Table I want to add the relevant amount of records to is called "tblB" "FieldB"

How would I go about writing a code for this?

An example would be very much appreciated.

Sorry to be a bit thick!

 

[Mile-O]

Just a quicky as almost done for the day:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter AS Long

Set db = CurrentDb
Set rs = db.OpenRecordset("MyTable")

With rs
    For lngCounter = 1 To CLng(Me.MyCombo.Column(1))
        .AddNew
        .Fields("MyField") = myValue
        .Update
    Next lngCounter
    .Close
End With

Set rs = Nothing
Set db = Nothing

 

[Crampton]

Thanks for the code.

I tried it but I keep getting the message "Invalid use of Null"
and the debugger highlights

"For lngCounter = 1 To CLng(Me.MyCombo.Column(1))"

Is it something I am doing wrong?

(Ps-I think I have put the correct Field names etc in the right places )

Thanks

 

[Mile-O]

MyCombo needs to be changed to the name of your combobox. The number in the Column() needs to be changed to reflect which column in your combobox you are wanting to reference. The first column is Column(0).

 

[Crampton]

Thanks Mile-o-phile that seems to have done the trick.

It has not quite worked as exactly as I hoped but I have tweaked it a bit.

Thanks for your help.