access.. macro or VB?

[Guest]

i am trying to create a macro that:
once clicked within a form the macro will lookup whether the records in a
table have a yes or no value in the tickbox.
If the record returns yes, it will tick another yes/no box - different field
- (within the same record) with a yes.

The field names are: "Manifest" Y/N and then assigning abother tick if
manifest = yes to fireld called "printedmanifest" Y/N.

Overall i am trying to create a macro or VB code that will lookup a record,
test if it has a yes value, if it does then assign a yes value to another
field within that record.


I am using access 2000.

 

[Arvin Meyer [MVP]]

Probably faster and easier to use an update query. I don't think you can do
it with a macro. You can do it with VBA code by building a recordset and
walking through it. An update query is actually rather easy and runs much
faster than code:

UPDATE tblMyTable SET YN2 = True
WHERE YN1=True;

 

[Guest]

Thanks arvin, That worked a treat. Just one more question

I am now trying to create a report that runs all the records with a "yes"
manifest field value, but do this before creating the Yes printedmani field.
So therefore I need to create a macro group that first print screens a
report, and the simultaneously runs the update query? is this possible?

 

[Arvin Meyer [MVP]]

Andrew it is far easier for me to write code than to try and figure out a
macro. First create a report (you can save the current screen as a report
and get rid of the colored background so that it looks proper). Use a query
for the report's recordsource which has a criteria of False for the rows to
be printed. Create a button and name it cmdPrint, in its click event put the
following code:

Private Sub cmdPrint_Click()
Dim strSQL As String
Dim db As DAO.Database

Set db = CurrentDb
strSQL = "UPDATE tblMyTable SET YN2 = True WHERE YN1=True;"


DoCmd.OpenReport "YourNewReportName"

If MsgBox("Did the report print OK?", vbYesNo, "Printed?") = vbYes Then
db.Execute strSQL
End If

End Sub

Substitute the correct names for everything and you're done. The above code
will print the report, then ask you if everything's OK before updating the
table. If the answer is no, fix the printing problem and push the button
again.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Arvin, Thanks again. Worked wonderfuly.
One final Question:

I am now trying to create a query in design view where the query returns the
'Datein' field if, there is no entry in that field. ifTherefore if the field
is empty it runs that record in that query. Wondering what needs to be put
into the critiea section?

If you live in australia i'll buy you a beer sometime to say thanks.

Cheers mate

 

[Arvin Meyer [MVP]]

Hi Andrew,

I'm not sure I understand your question. Design View of what? The query?
report? form?

What is Datein? Is it bound to the underlying data or unbound and filled in?

If Datein is empty, do you want all records? Or the records for a default
value?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com