Entering a multiple number of records based on a quantity in text box

[RMTechie]

Is there a way to enter a quantity of records equal to a number in a
text box?

For instance,

Let's say I have a:

Combo box with the options: Breakfast, Lunch, Dinner
Text box with date
Text box with the number of records I want entered
Command button to add records

The record would have only the date and meal type as fields.

I want to let the user select a meal type, give the number of records
(people) and click the button and add a record for each. For various
reasons, I can't just have a total quantity for that date, I need
separate records.

Any help?

 

[Linq Adams via AccessMonster.com]

Place an unbound textbox, call it NumberCopies.

Private Sub cmdMultiCopy_Click()
Dim Copies as Integer

If Me.Dirty Then Me.Dirty = False

If Not IsNull(Me.NumberCopies) then
Copies = Me.NumberCopies
Else
Msgbox "You must first enter the number of records to generate!"
Me.NumberCopies.SetFocus
End If

For I = 1 To Copies
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
Next I

End Sub

Enter the dtae and meal type in the textbox then press the command button.

 

[Linq Adams via AccessMonster.com]

Note: Sorry, the last post got sent before I removed HTML tags used yesterday
to answer an identical question elsewhere!

Place an unbound textbox, call it NumberCopies.

Private Sub cmdMultiCopy_Click()
Dim Copies as Integer

If Me.Dirty Then Me.Dirty = False

If Not IsNull(Me.NumberCopies) then
Copies = Me.NumberCopies
Else
Msgbox "You must first enter the number of records to generate!"
Me.NumberCopies.SetFocus
End If

For I = 1 To Copies
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
Next I

End Sub

Enter the date and meal type, then in the textbox eneter the number of
records you need, then press the command button.

 

[Dale Fye]

Keeping Linq's motto: There's ALWAYS more than one way to skin a cat!
in mind, I would do this with another technique.

1. I have a table in almost every one of my databases (tbl_Numbers) which
contains a single field lngNumber, and ten values (the numbers 0 to 9).
2. I also have a query that goes with it (qry_Numbers) that generates the
numbers from 0 to 99 (it is easy to expand this to 999 or 9999 based on your
needs) that looks like:

SELECT Tens.lngNumber * 10 + Ones.lngNumber as lngNumber
FROM tbl_Numbers as Tens, tbl_Numbers as Ones

3. In the command button that generates the records (cmdMultiCopy) I would
put code similar to:

Private Sub cmdMultiCopy_Click

Dim strSQL as string

On Error goto AddRecError
strSQL = "INSERT INTO tbl_yourTable (DateFieldName, MealFieldName) " _
& "SELECT DateAdd('d', lngNumber, #" & mt.txtDateField & "#, " _
& me.cbo_MealType & ") "
& "FROM qryNumbers " _
& "WHERE lngNumber < " & me.txtNumberOfRecords
Currentdb.Execute strSQL, dbFailOnError
Exit Sub

AddRecError:
msgbox err.number & vbcrlf & err.description

End Sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.