Routine to "batch" update 2

[keysol]

I’m creating a database to track 2,000 lotterie ticket sales.All 2,000 tickets have already been created in the table. I have a first form that identifies the buyer, ticket number, prizes won if any and wether the ticket is unsold and but on consignement to a seller. I wish to create a second form where I could update in batch mode those tickets given on consignement. For exemple tickets 95 to 125 could be on consignement to Mr. SoAndSo .What would be the code behind the accept command button that would refer to TextBox 1 (beginning ticket number), TextBox 2 (ending ticket number) and TextBox 3 (Seller name) and update records 95 through 125 in my table.

 

[PaulBricker]

If I understand you it would be something like this.

Dim rst as DAO.Recordset
Dim x as Integer
Set rst = CurrentDb.OpenRecordset("TableName",dbOpenDyanset)
For x = Forms!frmName!StartValue To Forms!frmName!EndValue
rst.AddNew
rst!TicketNumber = x
rst!Consignment = Forms!frmName!Consignmentfield
rst.Update
Next

This adds a record for each ticket 95 thru 125 and sets the consignment value to Mr. SoandSo. If you already have records for tickets 95 thru 125 and you just want to update the Consignment field then it would work like this.

Dim rst as DAO.Recordset
Dim strSQL as String
Dim x as Integer
For x = Forms!frmName!StartValue To Forms!frmName!EndValue
strSQL = "Select * From TableName Where TableName.TicketNumber = " & x
Set rst = CurrentDb.OpenRecordset(strSQL,dbOpenDyanset)
rst.Edit
rst!Consignment = Forms!frmName!Consignmentfield
rst.Update
Next

Hope this helps

Paul

 

[keysol]

I got this error message with Dim rst As DAO.Recordset highlighted:

Compile error:
User-defined error not defined

When I entered the following code:

Private Sub AccepterLot_Click()

Dim rst As DAO.Recordset
Dim strSQL As String
Dim x As Integer
For x = Forms!Calendriers_2004!ConsignéDébut To Forms!Calendriers_2004!ConsignéFin
strSQL = "Select * From Calendriers_2004Where Calendriers_2004.Numéro = " & x
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDyanset)
rst.Edit
rst!Consignment = Forms!Calendriers_2004!Vendeur
rst.UpdateNext

End Sub
******************************************
Form name= Calendriers_2004
StartValue= ConsignéDébut
EndValue= ConsignéFin
TicketNumber = Numéro
Consignmentfield = Vendeur
TableName = Calendriers_2004 (table has same name as form)

Gerr

 

[PaulBricker]

Sorry Gerr, there was a typo in the Set rst line. It should be
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

In my example I spelled Dynaset incorrectly. I got the 'n' and 'a' backwards.


Paul

 

[keysol]

Hi Paul,

I still get the same error after correcting the spelling!

Gerr

 

[RoyVidar]

Hi!

Sorry to barge into your discussion.

PaulBricker - your example is DAO written for Access 2000+ versions.

To use this code in Access 97, remove the DAO references in the declaration, to use it in versions 2000+, be sure to go to Tools | References and check the Microsoft DAO 3.6 Object Library.

And Keysol

change your
rst.updatenext to rst.update

and below that line, add the line:
next x

 

[keysol]

Well, RoyVidar,

I do know what you mean about Microsoft DAO 3.6 Object Library, but "References" doesn't appear under tools!
I'm using Access 2000 (9.0.3821 SR-1)

 

[RoyVidar]

Oups - In the VBA environment, not Access, it should be the first available menuitem.

If it's not in the checked items at top, browse down to find it, check it, then OK, and this code SHOULD work.

HTH Roy-Vidar

BTW - If you'd like, I'll "convert" it to ADO for you.

 

[PaulBricker]

Thanks Roy. I didn't notice rst.update and next run on. Gerr doesn't say which version they are using for this but it might also be a library problem if they are using A2000 or newer.

Gerr if you are using A2000, Open your module in design view, on the menu bar go to Tools....References
and look for the Microsoft DAO 3.6 object library and make sure it has a check mark in the box next to it. A2000 does not set a reference to the DAO library as a default so you need to set it yourself.

I just got called into work but will check back as soon as I can.

Paul

 

[RoyVidar]

Another short "Oups" - you need a space in your sql statement, between the Calendriers_2004 and Where, in addition to correcting the dbopendyanset to dbopendynaset, Roy-Vidar

 

[keysol]

Thanks again,

I seem to be making some headway.
I now have a run-time error '3265'
Element not found in this collection. (rough translation from french),

with this line highlighted:

rst!Consignement = Forms!ConsignationEnLot!Vendeur

I'm not sure about rst!Consignement

Gerr

 

[RoyVidar]

Hi!

rst!Consignement refers to the field Consignement in your recordset. Check the typing both in your table and code. (I prefer strongtyping fields I wan't to update in my sqlstring:

"Select Consignement from Calendriers_2004..."
Forms!ConsignationEnLot!Vendeur refers to the control Vendeur on the form ConsignationEnLot. Everything must be with correct spelling, and the form needs to be open.

HTH Roy-Vidar

(and the translation is more than good enough, i thik "item" is the word)

 

[keysol]

God bless you all,

I (WE) got it to work. My last problem was caused by referring to a form field instead of the table field.
I tested it and it does the job beautifully.

My final routine is:

Private Sub AccepterLot_Click()

Dim rst As DAO.Recordset
Dim strSQL As String
Dim x As Integer
For x = Forms!ConsignationEnLot!ConsignéDébut To Forms!ConsignationEnLot!ConsignéFin
strSQL = "Select * From Calendriers_2004 Where Calendriers_2004.Numéro = " & x
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rst.Edit
rst!ConsignéÀ = Forms!ConsignationEnLot!Vendeur
rst.Update
Next x

End Sub

Thanks again

Gerr