Membership Cards

[paul.raeburn]

I have a membership database with over 1700 records held in it, I want to print a membership card for each member each year here is my problem:

I want to print a selection of records i.e. abc, def etc.
I want to mark records that have had the card printed for this year.
I want to be able to re-run a card if anything is incorrect in the detail or goes wrong at print time.
I do not want to print all the records at once.

Can anyone suggest a way forward?

Many Thanks
Paul R

 

[llkhoutx]

Here's what I would do:

1. Add a Yes/No field to the membership table indicating membership card printed.
2. Add a DateTime field to the membership table indicating the date and time a membership card "batch" was printed.
3. Use a query to select the cards to be printed. When printing is complete, i.e. after your OpenReport command, use a query to update the two fields (Use only one Date and Time per print job).
4. To reprint a batch, query using the print job Date and Time.

"Hook 'Em" (Texan for Cheers)

 

[paul.raeburn]

You say in serial 3 that you use a query to print the forms, how do i get the user to pick say all names that start with 'd,e,f' etc.

Thanks

 

[llkhoutx]

Here's two ways to do this:

1. Use a series of check boxes or radio buttons.
2. Use a textbox serially

Build a query based upon either of the following.

Checkboxes (where values are -1 for selected, 0 for not selected):

sql="SELECT * FROM YourTableName "
swhere = ""
if chkA then
swhere="WHERE sMemberName Like " & chr(34) & "A" & chr(34)
end if
if chkB then
if len(swhere)=0 then
swhere="WHERE sMemberName Like " & chr(34) & "B" & chr(34)
else
swhere=" AND sMemberName Like " & chr(34) & "B" & chr(34)
end if
end if

etc.

if chkZ then
if len(swhere)=0 then
swhere="WHERE sMemberName Like " & chr(34) & "Z" & chr(34)
else
swhere=" AND sMemberName Like " & chr(34) & "Z" & chr(34)
end if
end if

sql = sql & swhere

Now use sql as the recordsource of your report.

TextBox:

Use a button adjacent to the Textbox.

private sub Button_Click()
if stxtBox & "" = "" then exit sub ' nothing to append to criteria
'sc is another textbox on your form to hold the criteria you build
if len(sC) = 0 then
sc = " swhere " & chr(34) & stxtBox & chr(34)
else
sc = " AND " & chr(34) & stxtBox & chr(34)
end if
END SUB

Once the criteria is built, append it to a SELECT string, use it as a report criteria and generate your report of membership cards.

Another way that comes to me is to use a series of raised textboxes on your form for each letter of the alphabet. When a textbox is clicked, sink it. Then on another button click, check which are sunken and build your sql strink and criteria accordingly. When you generate your report, raise all criteria buttons.

You could also have a limited number of textboxes, and if non-null use to build your criteria.

The foregoing is sketchy, but should get you started. You've got to brute force it, no matter what you do.

 

[paul.raeburn]

I have taken the check box option, as below, I would like to check now that the cards have not been printed before, how can I wrap a check around these.

If Me.chk_v < 0 Then
If chkOption Then
stOption = stOption & " OR "
End If
stOption = stOption & "[Surname] Like 'V*' Or [Surname] Like 'W*' Or [Surname] Like 'X*' Or [Surname] Like 'Y*' Or [Surname] Like 'Z*'"
chkOption = True
End If

' Check a box has been selected or the SQL will cause an error
If chkOption Then
' Open the report
stDocName = "rpt_membership_card"
' Report will close and give msg if no data as we Cancel in the NoData event in the Report
' This will raise an error (2501) which is caught the resumed
DoCmd.OpenReport stDocName, acPreview, , stOption

' Set printed flag (membership_card_print to -1) where a card has not been printed
' and where the surnames are in the list (sOption)
ssql = "UPDATE Member_Detail SET membership_card_print = -1 WHERE membership_card_print = 0 AND " & stOption
DoCmd.SetWarnings False
DoCmd.RunSQL ssql
DoCmd.SetWarnings True​

Thanks

 

[llkhoutx]

Use query qTemp as your report recordsource, then modify the following erroneous code (the ssql you build is the recordsource for the report):

DoCmd.OpenReport stDocName, acPreview, , stOption

' Set printed flag (membership_card_print to -1) where a card has not been printed
' and where the surnames are in the list (sOption)
ssql = "UPDATE Member_Detail SET membership_card_print = -1 WHERE membership_card_print = 0 AND " & stOption
DoCmd.SetWarnings False
DoCmd.RunSQL ssql

DoCmd.OpenReport stDocName, acPreview, , stOption

dim db as dao.database
dim qdf as dao.querydef
set db=currentdb
'delete the query (one must exist or you get an error), then recreate it
docmd.deleteobject acquery,qTemp
'creates new qTemp
set qdf=db.createquerydey(qTemp,ssql)
qdf.close
db.close
'qTemp has been set as the recordsource of the report
DoCmd.OpenReport stDocName, acPreview, , stOption

The ssql you execute is too late, the report is already open. Delete that code.

 

[Pat Hartman]

I would not use a printed flag. There is no way to tell if the card was printed yesterday or two years ago. Since the cards are annual, I would use a field that holds the year for which the card was issued. So when printing cards for next year, you would enter 2005. When printing cards for this year, you would enter 2004. Just make the IssueYear a parameter.

 

[llkhoutx]

Pat - you're missing the point that the cards are printed in batches and sometimes reprinted.

 

[Pat Hartman]

I don't think I missed the point.

I want to mark records that have had the card printed for this year.

There are marks and there are marks. I simply suggested using an intelligent mark rather than an unintelligent one. The problem with using a yes/no mark is that unless you remove the mark at some point, it is still there next year when you want to print membership cards again. Someone has to remember to remove the marks or you need to set up some method to prompt yourself when it is time to remove them. With a year indicator (an actual print date would work but not as well), you don't have to clear out old data. You can tell it is old by looking at it.

The marks will not be used to control printing. They are simply there to indicate that a card has been printed. They could be used to find out if any cards were missed. You would simply look for members whose issued year was not equal to the year you are working with.

Anytime a card is printed, the issue year should be updated regardless if the card has already been printed for this year. If having multiple cards floating around could cause problems, you can add a count field. The count field would be set to 1 if the existing issue year is different from the one you are updating to otherwise the count field would be incremented. That way you will know who is asking for multiple cards and how often they have done it. Of course if you aren't keeping history you won't have any year to year comparison but you could just print a report at year end of all members who have had multiple cards issued and file it away. You can then check that list when someone asks for a new card.