Propagate entries into table based on subform field number (1 Viewer)

Leyton01

Registered User.
Local time
Today, 22:32
Joined
Jan 10, 2018
Messages
80
I am attempting to create a mail merge database which can create printable labels and need to increase the number of generated labels of certain types based on user input.

I have a main form which has some fields which appear on every label (name and DOB) then I have created a subform which contains a field "Selected" (Yes/No), "Medication" (text), "Strength" (text) and then a "Copies" (number, limited to 1-9) stored in a table tblTempMedications.

The subform is prepopulated with some default data and users can select these or enter new entries as they see fit. I have another temporary table set up (tblMailMerge)which is linked to a Word Mail Merge.

I am wanting to set up a button which users push that creates a new entry for all records which have "selected" ticked BY the number in the "Copies" field for that record. i.e. if the users clicks the check box "selected" for a line and then selects "3" in the copies field I need that information copied 3 times to the temporary table. (the "copies" field does not need to come across, only multiply it). It then moves on to the next selected record and creates an entry based on that data and copy number.

The record consists of the default information "Name" "DOB" "Today's Date" and then the information stored in the record in the subform.


I have all the subsequent coding set up to create the mail merge for each record in the tblMailMerge so that is why I am trying to do it this way.

Can someone suggest how I step through the SQL insert commands taking into account the "copies" value?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:32
Joined
Feb 19, 2013
Messages
16,555
you need to use a loop - something like

Code:
dim I as integer
for I=1 to me.copies
   'execute sql
next i
 

Leyton01

Registered User.
Local time
Today, 22:32
Joined
Jan 10, 2018
Messages
80
I'm just not sure how to incorporate that loop into stepping through the records which are "selected" and using the copies value from the current record only (me.copies referring to the current record)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:32
Joined
Feb 19, 2013
Messages
16,555
without knowing your code or the final outcome required, cannot advise further.

All depends if you want say 3 labels for first selection followed by 3 labels for second selection etc or labels for all selections followed by 2nd copy of labels etc
 

Leyton01

Registered User.
Local time
Today, 22:32
Joined
Jan 10, 2018
Messages
80
I have a main form (frmMain) which has 2 unbound controls ("Name" and "DOB") then a subform (sfrmMedications) in datasheet view with 4 fields "Selected" (Yes/No), "Medication" (text), "Strength" (text) and "Copies" (number, limited to 1-9).

The sfrmMedication sub form is linked to a temporary table (tblTempMedications) which is pre-populated with some default data on creation and deleted on close (nothing needs to be saved). The user enters the name and DOB on the main form and then makes any changes on the subform that they want. This includes ticking any of the pre-populated records or adding new ones. They also set the number of labels they want using the "copies" field for that records.

When the user presses a button I want the selected records to be copied into the mail merge table (tblMailMerge), including the name and DOB and repeated by the number of copies.

EG Main Form -
Name: John Smith
DOB: 1/1/50

Sub form:
Selected | Medication | Strength | Copies
X | Med1 | 10mg | 2
.. | Med2 | 10mg | 1
X | Med3 | 10mg | 3

Will create in the tblMailMerge
John Smith | 1/1/50 | Med1 10mg
John Smith | 1/1/50 | Med1 10mg

John Smith | 1/1/50 | Med3 10mg
John Smith | 1/1/50 | Med3 10mg
John Smith | 1/1/50 | Med3 10mg


I was previously achieving this through using a listview and stepping through the selected records but I need to duplicate labels now and the old way can't handle copies.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:32
Joined
Feb 19, 2002
Messages
42,981
To multiply records, create a table with enough rows to satisfy the maximum repeat. If your max is 9, then the table has 9 rows numbered 1-9. Then your query joins to this table on the LabelCount Column

Select ... From MainTable Join RepeatTable on MainTable.LabelCount <= RepeatTable.RowNum Where MainTable.Selected = True;

Using this method, each selected record can have a different number of labels.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:32
Joined
May 7, 2009
Messages
19,175
add code to the subform's AfterUpdate event to add those records.
note that you need to change the fieldname on this code to the correct one:

Code:
Private Sub Form_AfterUpdate()
dim i as integer
	If Me.Selected=True Then
		for i = 1 to Me.[Copies]
			currentdb.execute _
			"insert into tblMailMerge ([name], [dob], [medication ] " & _
			"select " & chr(34) & me.parent![name] & chr(34) & ", " & _
			chr(34) & Me.Parent![DOB] & Chr(34) & ", " & _
			Chr(34) & Me.Medication & " " & Me.Strength & Chr(34) & ";"
		next i
	else
		'unticked ... shall we delete it
		currentdb.execute "delete * from  tblMailMerge " & _
		"where [name] & [dbo] & [medication] = " & _
		chr(34) & me.parent![name] &  _
		Me.Parent![DOB] & _
		Me.Medication & Chr(34) & ";"
	end if
End Sub
 

Leyton01

Registered User.
Local time
Today, 22:32
Joined
Jan 10, 2018
Messages
80
without knowing your code .....

Here is the code which was being used when it was a listbox and did not need to do multiple copies:

Code:
Private Sub btnMailMerge_Click()

Dim varItem As Variant
Dim strSQL As String

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblMailMerge;"
DoCmd.SetWarnings True

For Each varItem In Me.lstDrug.ItemsSelected

    strSQL = "INSERT INTO tblMailMerge (PatientName, CurrentDate, Medication) " _
                & "Values('"& Me.txtPatientName.Value & " (" & Me.txtPatientDOB & ")', '" _
                                    & Me.txtDate.Value & "', '" _
                                    & Me.lstDrug.Column(1, varItem) & " " _
                                    & Me.lstDrug.Column(2, varItem) & "')"
    CurrentDb.Execute strSQL, dbFailOnError

Next


End Sub

Then to allow the user to add new meds and select the number of copies I had to change to a subform but was unsure of the code to step through the copies.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:32
Joined
Feb 19, 2002
Messages
42,981
Are you trying to duplicate data or are you trying to print multiple labels per item. I gave you directions on how to do the second function. There is no need to create dummy data in a table do do this. The query does it all without creating database bloat.
 

Leyton01

Registered User.
Local time
Today, 22:32
Joined
Jan 10, 2018
Messages
80
I'm trying to duplicate data to print multiple labels.

the mail merge side is all set up - it points to the tblMailMerge and works fine. I just need to duplicate the entries in the Mail Merge table so that Word makes multiple pages which get fed through to the printer.

The old way was:

Table with default data shows in listview control -->
user selects relevant lines then presses button -->
temporary table (old) data gets deleted then populated with selected lines from listview.
(the mail merge is then run and is pointing at the temp table)

Because the information does not have to be saved clearing the temp table every time prevents database bloat. If the merge fails for whatever reason the information is still there until the user presses the create label button again.

The new setup that I am hoping for:
Table with default data populates subform -->
user makes changes including selecting relevant lines and setting number of copies then presses button -->
temp table data gets deleted then selected meds get copied to temporary table (tblMailMerge) and are repeated by the number of copies selected
(the mail merge is then run on this temp table resulting in multiple copies of the relevant labels).

Picture attached - it is a very simple database but I was unsure of how to go from listview to a subform with copies.
 

Attachments

  • pharmlabels.jpg
    pharmlabels.jpg
    70.4 KB · Views: 39

Leyton01

Registered User.
Local time
Today, 22:32
Joined
Jan 10, 2018
Messages
80
add code to the subform's AfterUpdate event to add those records.
note that you need to change the fieldname on this code to the correct one:

Code:
Private Sub Form_AfterUpdate()
dim i as integer
	If Me.Selected=True Then
		for i = 1 to Me.[Copies]
			currentdb.execute _
			"insert into tblMailMerge ([name], [dob], [medication ] " & _
			"select " & chr(34) & me.parent![name] & chr(34) & ", " & _
			chr(34) & Me.Parent![DOB] & Chr(34) & ", " & _
			Chr(34) & Me.Medication & " " & Me.Strength & Chr(34) & ";"
		next i
	else
		'unticked ... shall we delete it
		currentdb.execute "delete * from  tblMailMerge " & _
		"where [name] & [dbo] & [medication] = " & _
		chr(34) & me.parent![name] &  _
		Me.Parent![DOB] & _
		Me.Medication & Chr(34) & ";"
	end if
End Sub

This is the correct idea - thanks @arnelgp.

It doesn't quite fit into the workflow above but I may be able to change it to suit. I will have to move the clearing of the data from the temp table to the form load probably and include a clear all button as before the routine only ran on a button press at the end but this runs after every change.
 

Leyton01

Registered User.
Local time
Today, 22:32
Joined
Jan 10, 2018
Messages
80
Unfortunately the logic from @arnelgp does not work - if a user accidentally selects 2 copies and then increases it to 3 then it creates 5 entries. Also if they change the name of the medication it will not delete the old record as it cannot match the medication name.

I think it would be best to let the user make all changes then have the table populated after they press a button rather than use the after update event. Trying to track all changes dynamically is difficult.
 

Leyton01

Registered User.
Local time
Today, 22:32
Joined
Jan 10, 2018
Messages
80
And Pat - I am sorry but I don't understand your explanation and how I can apply that to the problem - that is my shortcoming not yours.
 

Leyton01

Registered User.
Local time
Today, 22:32
Joined
Jan 10, 2018
Messages
80
I did use a loop in the end through a recordset - this is how I achieved it:

Code:
Dim rs As DAO.Recordset
Dim i As Integer
Dim SQLStr As String

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblMailMerge;"
DoCmd.SetWarnings True

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTempMedications")

'Check to see if the recordset actually contains rows
If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rs.EOF = True
    If rs("Selected") = True Then
            For i = 1 To rs("Copies")
     SQLStr = "insert into tblMailMerge ([DoctorName], [PatientName], [CurrentDate], [Medication]) " & _
            "select " & Chr(34) & Me.lstDoctor.Column(1) & Chr(34) & ", " & Chr(34) & Me.txtPatientName.Value & " (" & _
            Me.txtPatientDOB.Value & ")" & Chr(34) & ", " & Chr(34) & Me.txtDate.Value & Chr(34) & ", " & _
            Chr(34) & rs("Drug") & " " & rs("strength") & Chr(34) & ";"
                        
            CurrentDb.Execute SQLStr
        Next i
     End If
        rs.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If

rs.Close
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:32
Joined
Feb 19, 2002
Messages
42,981
Create a table with 6 rows. One field named RowNum with a value of 1-6.

Create a query of your meds that includes the LabelCount for each med. Add this table. There won't be a join line. Add a Where clause

Where LabelCount <= RowNum

This query should duplicate each row the number of times to match the LabelCount.

If you can't get it to work, create a sample database with your table and I'll build the query.
 

Users who are viewing this thread

Top Bottom