Using a Query to insert records

[marnik]

I am playing with a labelling database project. I am creating labels that are sequential (specified by a sequence_start and sequence_end), these are numerical.

To create labels I have a report setup that refers to a query for the label info and sequences. I am wanting to create a setup where the sequence start and end create a INSERT that appends to a table with the sequence info for that client, that table can then be used to create the labels from.

In short, I enter a seq_start and seq_end, and a module/macro then adds those sequences to a master table.

If this is the wrong way to approach this . . . please help? I am running into dead ends.

Jeff

 

[Steve101]

Jeff,

Dont understand why you feel you need to create a table from which you then create the labels. You should be able to generate the labels DIRECT from the query, by making the query the recordsource for the label report.

The only time you might need to create/append to a table for this application, is if you NEED to keep track of when each individual has a label produced. If this is'nt required, then "go direct" from the query.

Hope this helps,
Steve

 

[marnik]

Here is a bit more info.... I do need to track what labels have been generated? I am looking for a way to: generate labels based on a given start and end value, if I enter all of the needed label values it works fine right now. But it isn't useful, in some case we need to generate 1000s of labels, so setting a start and end point just makes sense. I have been looking at the Access Report OnFormat property for the detail section, perhaps that can be used. I am fishing a bit for ideas. The jist of it: enter a start and end value, and print labels with those values on it.

Jeff

 

[Steve101]

Jeff,

(a) When you say Sequence Start and End values, are these sequences related to Customer Numbers or what?

(b) I would recommend against trying do code against the OnFormat event in this case. This can cause problems if you are previewing the data (backwards and forwards for example, can generate multi audit entries, and so on). Also can be a nuisance if the printer fouls up and the labels are lost and you have to run the program again.

(c) Option I recommend is to add another menu option, which allows the label entries to be generated AFTER they have been physically produced. Simple process; just use the same qeury used to generate the labels, to generate the new entries to the label log file. Of course you need to remember to run this step after the labels are produced, but its my preferred method.

Hope this helps; if you need help with the code, pls advise,

Cheers,
Steve

 

[marnik]

Hi Steve

Please provide some code for what you suggest...

Thanks

Jeff

 

[Steve101]

Jeff,

Here's some code that you can hopefully adapt:

Function CreateLabelLog()

Dim DB As database: Set DB = CurrentDb
'-------------------------------------------
'Declare input recordset. Modify SELECT
'Statement to match report's recordsource
'-------------------------------------------
Dim RSI As DAO.Recordset
RSISql = "SELECT * " & _
"FROM tblYourTable " & _
"WHERE ..add conditions here "
Set RSI = DB.openrecordset(RSISql)

'----------------------------------------
'output recordset; For each report label,
'generate a log record.
'----------------------------------------
Dim RSO As Recordset
Set RSO = DB.openrecordset("tblLabelLog&quot

'---------------------------------------------
'Now iterate through the input set, generating
'the output log entries
'---------------------------------------------
While Not RSI.EOF
RSO.AddNew
RSO("SomeOutputField1&quot = RSI("SomeInputField1&quot
RSO("SomeOutputField2&quot = SomeOtherValue
....
RSO.Update
RSI.MoveNext
Wend

'----------
'Close shop
'----------
RSO.Close
RSI.Close
DB.Close

End Function


Hope this helps.

Cheers,
Steve