Simple problem, please help... 1

[SpandexBobcat]

Hi folks,

I have a form with a Print button (to print the current form data) Once the record is printed I want a 'Printed?' checkbox to show yes (ticked) I have been trying doing this via an update query, triggered in the vb code. The problem I have is it sets 'yes' for EVERY record, not just the one in question.

The SQL for the query is:

UPDATE ContactInfo SET ContactInfo.Printed = Yes;

How can I specify that only the currently displayed record on the form is set to 'Yes' and not the entire table...

Thanks in advance,

SpandexBobcat

 

[lespaul]

Add a where clause to your query:

WHERE SomeKeyField = frmName!SomeControlOnFormWithKey

Leslie

 

[glalsop]

What is the field that uniquely identifies the record? You could use that in the SQL statement:

DoCmd.RunSQL "UPDATE ContactInfo SET ContactInfo.Printed = Yes WHERE ContactInfo.YourUniqueID = " & Me.YourUniqueIDField & ";"

-Gary

 

[SpandexBobcat]

Hi there. Thanks to both of you for your input. I have tried to implement the code you have given but I can't quite get it to work...

The unique identifier is an autonumber called 'Auto' (I know its bad practice but it's not important for the end result) The form is based on a query, 'RCfilter', derived from the table 'ContactInfo' and the form name is 'ContactInfoRC'.

I have used this SQL code in the query:

UPDATE ContactInfo SET ContactInfo.Printed = Yes
WHERE [ContactInfo].[Auto] = Me.Auto;

but when the print button is pressed it is asking for the Me.Auto value... On entering a value it updates the record but it doen't seem to be able to pull the value of Auto by it's self...

Where am I going wrong?

 

[Crowley16]

you can't use Me.Auto in your query, since Me doesn't refer to anything...

you have to use the name of the form, and then the name of the control/field, e.g.

FormPrinting!Auto

you might want to include a textbox bound to this Auto field in your form somewhere, and make it not visible or something so you can reference it this way...

 

[SpandexBobcat]

Hi Crowley16,

I thought that Me.Auto ment the present value of Auto for the record on the form... My mistake.

I have tried using FormName!Auto but still get the same outcome. At present I have a textbox for Auto that is set to visible, although it won't be once this is working..... If i get it working....

 

[OhioSteve]

This is my suggestion:

1) Create a field on your form for auto. Call it "myfield". You may want to set myField's visible property to "false".
2) In the update query add a "where" clause.
3) The criteria for the where clause should be "Forms![xxx]![myField]". XXX is the name of your form.

My solution is almost identical to lespaul's idea. I am just giving more explanation.

 

[SpandexBobcat]

Ok, I have carried out your suggestion Steve but it is setting the 'Printed' field to Yes on all the records, not just the one on the form... Here is the code I used:

UPDATE ContactInfo SET ContactInfo.Printed = Yes
WHERE [Forms]![ContactInfoRC]![AutoF];

Have i missed something out because this getting a bit frustrating!

Work like you don't need the money and Dance like nobody's watching.

 

[Crowley16]

eh, are you saying you're using that code in the recordsource of your form?!

if that's the case, then of course it'll set all recs to YES because it'll set every record u open the form with...

Going back to your original description:
put this in the onClick() event of your print button...

docmd.runsql( _
   "UPDATE ContactInfo SET Printed = TRUE WHERE Forms!ContactInfoRC!AutoF = '" & me.AutoF & "';" _
)
me.requery

you might have to edit it a little

 

[SpandexBobcat]

No, not in the recordsource.... the record source for the form is a query called RCfilter... derived from the parent table ContactInfo.... I have been building an update query to set the field Printed to yes for the currently open record...

I will have a looksy at putting your suggestion into the onClick() event instead......

Work like you don't need the money and Dance like nobody's watching.

 

[Crowley16]

there's no need to use an update query to do that...

the simplest way would just be to explicitly set the tickbox to true in the onClick() event of the print button.
No need for any invisible textboxes or sql or anything...

just:
tbxPrinted = True

I'm assuming that your form is bound...

 

[SpandexBobcat]

Thanks Crowley16, it worked a treat.... and sorry to everyone else for the run-around this caused.

I thought there must be an easier way of doing it I just couldn't see it...

For your valiant effort and lasting patience I award you a star Crowley16. Thanks again.

Simon