vba loops

[Cowboy_BeBa]

hi,

i know theres a similar (maybe even identical) thread already up, but i cant seem to apply the other thread to what i need

i cant really go into specifics about what im doing without it taking 10 pages so ill try and summarize my situation properly and quickly,

**If you want you could probably just skip to the bottom and read the last paragraph and the code... that might be all the info you need to help me out**

basically im designing a db to keep track of products being produced at a warehouse, ive designed a table and form (tblBatchPrint) that has a multivalued list box of all our products, as well as other info such as production date, best before date, the number of batches being produced (there are 6 batch fields lettered A-F, each could contain a different number), using this table we can now print all our production values for the month at once instead of printing them one by one like we used to do (when you have 40 products, it starts getting time consuming),

the problem is one of my reports needs to have one page per batch, since each record has a batch A, batch B, etc, access wont let me treat them as if they were different records, which is what i need,

ive designed a table that stores a product name (tblBatches), a batch letter and a batch size (which is all i need), then designed a query to display all the records in tblBatchPrint, qryPackSum (this query seperates the mutivalued product list, so that each product selected has its own record, as i need to print 1 of each batch for every product produced),

finally (and this is where im stuck) ive written a function (PackPrint) that goes through one record in qryPackSum and creates values in tblBatches appropriately ive incorporated it into the button code below (the docmds just prepare the tables and queries for the PackPrint function

Private Sub Command45_Click()
DoCmd.RunSQL "DELETE tblBatches.* FROM tblBatches;"
DoCmd.OpenQuery "qryPackSum", acViewNormal, acReadOnly
DoCmd.GoToRecord acDataQuery, "qryPackSum", acFirst
DoCmd.OpenForm "frmBatches", acNormal, , , acFormEdit


PackPrint


DoCmd.Close acForm, "frmBatches", acSaveYes
DoCmd.Close acQuery, "qryPackSum", acSaveNo
End Sub

so far it works perfectly with the first record of qryPackSum, now all i need to do is create a loop that repeats PackPrint for every record on qryPackSum, (doesnt need to repeat the docmds at the start and end, just the function)

any help would be appreciated because im not sure how to get this going

 

[Cowboy_BeBa]

okay, so far ive been playing with a few loops and none of them are generating any values in tbl batches, i think the only thing im having trouble with is setting a condition for the loop, im currently using

Do While DLookup("ProdName", "qryPackSum") <> Null
PackPrint
DoCmd.GoToRecord acDataQuery, "qryPackSum", acNext
DoCmd.GoToRecord acDataForm, "frmBatches", acNewRec
Loop

since its not working im guessing prodName in qrypacksum keeps returning a null value, ending the loop before it even begins, anyone know of a way to determine whether the current record selected is an existing record or a new one?

 

[SpentGeezer]

This may help, it is crude and untested though:

'get a record count
Set rdset = CurrentDb.OpenRecordset("qrypacksum")
rdset.MoveLast
rdset.MoveFirst
reccount = rdset.RecordCount
Set rdset = Nothing

DoCmd.OpenQuery "qryPackSum", acViewNormal, acReadOnly
DoCmd.GoToRecord acDataQuery, "qryPackSum", acFirst
DoCmd.OpenForm "frmBatches", acNormal, , , acFormEdit



If reccount = 0 Then
    MsgBox "There are no records", vbExclamation, "Error"
    Exit Sub
ElseIf reccount = 1 Then
    packprint
    Exit Sub
Else
    a = 1
    Do Until a = reccount
        packprint
        DoCmd.GoToRecord acDataQuery, "qryPackSum", acNext
        DoCmd.GoToRecord acDataForm, "frmBatches", acNewRec
        a = a + 1
    Loop
    packprint 'to get the last one
End If

Doesn't this "DoCmd.GoToRecord acDataForm, "frmBatches", acNewRec" go to a new record though??

Good luck with it

 

[Cowboy_BeBa]

thanks spent, i used the get a record count function and set the do while function to untill recount=ticker (ticker is one i set up to count how many times the code was executed), worked perfectly,