Create Multiple Records Unbound Form

[mcktigger]

Hi All

I've been trying to figure out this problem for a few days now and getting no where fast.

I have an unbound form used for pricing products and services. The form shows sell price, qty, margin etc and the user enters the qty if selling that product. I want to take this data (ProductID, Qty, SellPrice) and automatically create multiple lines in tblquotelines if the user enters a qty against a product. Basically I'm trying to build up a customer quote with multiple lines from one single pricing screen.

There could be 1 product or 15 products with a qty so I guess I need to loop through each line on the form to determine if a record is to be created. However I don't kno wif this is possible from an unbound form or how to code this.

Any help would be much appreciated.

 

[Trevor G]

What about something like an append query and against a textfield with a number in it, it then loops through and appends the records to the value of the textfield.

Take a look at this loop to get you going. In this instance it is opening a report this can be changed to a query. So you would set up an append query to refer to the form unbound textboxes.

Access code to print out a report multiple times based on a text box
Dim intNo As Integer

Do Until intNo = Me.NoOfTimes
DoCmd.OpenReport "reportName", acViewNormal, , "ID= " & Me.ID
intNo = intNo + 1
Loop

 

[mcktigger]

Hi Trevor

Thanks for your reply. I can see how that could work but not sure how the field me.NoofTimes would be incremented on the form.

Are you saying this would count the number of qty fields with value > 0? The fields on the form are unbound so I couldn't count the form's records.

Thanks again

 

[Trevor G]

If its the quantity field then you would use that as the amount of records it needs to create.

The append query can point to each form control, if you would prefer code to generate each record in the table you would need something like this (Not tested)

Function addrecords()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intNo As Long
Set db = CurrentDb
Set rst = db.TableDefs("Name goes here")
Do Until rst.EOF
rst.AddNew
rst.Fields("First Field Name") = txtFieldName.Value
rst.Fields("Second Field Name") = txtFieldName2.Value
rst.Update
rst.NextRecordset

Loop
intNo = intNo + 1
End Function