Add multiple records through loop.

[hende10]

I am in the planning stage of building a database, and I wanted to check how possible and/or plausable something is.

Simply put, I have a two field table called "Table1", with fields "Ticket" (autonumber) and "Employee" (text). Through user prompts, I want a form or query to add x amount of records with the specified employee name.

The user would be prompted for how many records to add, and what Employee name, then the records would be created. And, if possible, I would like to return the autonumber "Ticket" range that was just added for "Employee"

Any ideas?

Thanks,

Hende

 

[Trendsetter]

This would be reasonably easy to do with a For Loop

Dim i, booked as Integer
Dim name as string
Dim rst as recordset
Booked = Me.NumReqd - a control on the form
name = Me.EmployeeName - a control on the form
Set rst = CurrentDb.OpenRecordset("Table1")
For i = 0 to i = booked - 1
rst.addnew
rst!Name = name
rst.update
Next i
rst.close
Set rst = Nothing

You don't need to make any entry for the Autonumber as it will be generated when the new employee name is added.

You don't indicate how you would like the ticket numbers returned - they could be in a subform or a list box etc....

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[hende10]

Thank you Frank,

I've tried your code and it looks good, but I keep getting a type mismatch error on
Set rst = CurrentDb.OpenRecordset("Table1")
I'm sorry if the answer is obvious, I've never used much more then Macros in a database.

As far as the ticket numbers returned, I am jsut planning on using a message box.

Thanks,

Hende

 

[Trendsetter]

Does Table1 exist??

Try ("Select * FROM Table1")

Maybe it's me being lazy -
SQL should know what to do by now!

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[RoyVidar]

In VBE - Tools | References, set a reference to Microsoft DAO 3.# Object Library, then also explicitly declare DAO objects:

[tt]dim rst as dao.recordset[/tt]

But the whole process seems a bit, well, in lack of better words "strange". Why populate a table with the same name "umpteen" times? What happens if an employee changes name (marriages tend to happen from time to time)? Don't you have an employee table where you could fetch the primary key from?

Roy-Vidar

 

[hende10]

Yes, this does seem like a strange thing to do, but here's the situation....

We are starting to use scrap tickets here to monitor faulity products or work done in our shop. These tickets will be in hard copy form (prenumbered), and then entered into a database once the hard copy is filled out and turned in. These hard copies will be handed out in blocks to supervisors (i.e. Bob get TickNos 100-150), and one person is supposed to keep track of who gets what block in a spreadsheet. Any supervisor that skips a ticket number assigned to them no will be flogged and tortured, then the ticket voided.

I wanted to move the spreadsheet into the database, and assign the block ticket nos through the database. Then, if someone later tries to enter a invalid ticket number, it will kick back an error that it hasn't been assigned yet.

Any suggestions?

Thanks, Hende

 

[TheDude53]

the rs set line was right you just forgot the ,dbopentable. don't do a full select in an rs for all records if you don't have to!

ie:
Set rst = CurrentDb.OpenRecordset("Table1",dbopentable)

 

[rubbernilly]

Suggestion -

Master Table:
Name: Employees
PrimaryKey: EmpID
<<other fields as necessary>>

Slave Table
Name: TicketBlocks
PrimaryKey: BlockID (autonumber)
ForeignKey: EmpID (will be the ID of the supervisors)
Field: RangeLBound
Field: RangeUBound

So you would have Marvin the Manager as a record in your Employees table, with an EmpID of 13. If he were assigned ticket blocks 1-50, 101-150, and 251-300, he would have three entries in the TicketBlocks table:

BlockID   EmpID     RangeLBound     RangeUBound
  1         13           1              50
  2         13         101             150
  3         13         251             300

When a particular ticket comes in, you can either write the Supervisor's EmpID to the ticket via a lookup on this table, or you can just reference this table for any output of the ticket without writing the ID. The SQL lookup would be...

...WHERE TicketID Between TicketBlocks.RangeLBound And TicketBlocks.RangeUBound