Help on the impossible

[quinncooper1988]

hi,

I am creating a database for an approvals system. Long story short, I need to export data to an excel sheet to total up the amount of money spent on a certain budget code. However, I do not want to export amounts that have not been approved.

On the form I have a yes/no box, for items, and tick if it is approved, and blank if not approved.



What type of macro do I need to create to filter the data to export only approved items, I have tried running a query first, however no joy.

Can anyone help, or is this truly mission impossible?

Thanks

 

[boblarson]

You create a query first that similar to:

Select * FROM TableNameHere Where Approved = True

you should be able to get that put together by using the QBE grid.

Then you export the query (no need to "run" the query, just export it).

 

[quinncooper1988]

Thank you!! it works, woohoo.

Thanks alot!!!!

 

[boblarson]

Glad you got it sorted. Oh and by the way...

 

[quinncooper1988]

Thanks, I am glad I have joined now, so many kind people.


I now have another dilema. The data that I exported out, to find the "total" using excel. I now need to import back into Access, but I only need the total figure to be imported back into the form*. I then need a macro to do this for me, at a specific time once per week. Say mondays a 11.59PM. I can make the macro import, however, I do not know how to automate it.

The reason is, my seniors would like to see a running total of how much is spent on particular budget codes, within the approvals database.

*Can access not total up all the totals in a table? this would be a whole lot easier if it can.

 

[boblarson]

You can create a totals query. Just create a new query and use the query you created for the export instead of a table in it and then look for this button:

and click it and you can change the GROUP BY to SUM for the field you need.

 

[quinncooper1988]

You are a legend!! THANKS

 

[quinncooper1988]

I have I think one final one.

Can a macro, be used to change the status of a form to read only, but only for the previous forms... So all future records in the form can be edited, but say on a monday when you click to macro,

all the recordings previous, will be read only, so no one can go back and edit any data.

Thanks

 

[boblarson]

I have I think one final one.

Can a macro, be used to change the status of a form to read only, but only for the previous forms... So all future records in the form can be edited, but say on a monday when you click to macro,

all the recordings previous, will be read only, so no one can go back and edit any data.

Thanks

Are you just saying that for ANY date previous to today you would like it not to be able to be edited and for any in today you can? Do you have a date field to know when the record was entered?

 

[quinncooper1988]

Yes there is the date on there.

Yeah, I need it so once the Macro has been pressed, all data previous to todays date is in a read only format. and anything forward of today is editible.

thanks

 

[boblarson]

In the form's On Current event put this:

If Me!YourDateFieldNameHere < Date() Then
   Me.AllowEdits = False
   Me.AllowDeletions = False
Else 
   Me.AllowEdits = True
   Me.AllowDeletions = True
End If

 

[quinncooper1988]

sorry i think it's me being dumb...

I can't find where to put this code... I put it in the box after you click the button "code", saved it, and nothin happens, I can still edit the data

I think Im doing something wrong

 

[boblarson]

sorry i think it's me being dumb...

I can't find where to put this code... I put it in the box after you click the button "code", saved it, and nothin happens, I can still edit the data

I think Im doing something wrong

It goes in the FORM's On Current event, not the button's code.

See here:
http://www.btabdevelopment.com/main/QuickTutorials/Wheretoputcodeforevents/tabid/56/Default.aspx

 

[quinncooper1988]

I have done that now,

thanks that link provided useful info.

When i go back into the form, it says macro has not been saved.

So I create a new macro: past the code you provided and says sytex error.


Im sorry for being a pain, I used to be really good at access, then since I left college, I never used it and forgot most of it.

 

[boblarson]

You don't create a macro. Delete any macro you may have done for this. This is VBA. So, in the dropdown of the event (on current) you select [Event Procedure] and click the ellipsis (...) next to it and it opens the code window and then you put the code in there.

 

[quinncooper1988]

I put the following:


Private Sub Form_Current()
If Me!Date < (7 / 1 / 2009) Then
Me.AllowEdits = False
Me.AllowDeletions = False
Else
Me.AllowEdits = True
Me.AllowDeletions = True
End If
End Sub



No error messages are displayed, but, I can still edit

 

[quinncooper1988]

If Me!Date < (7 / 1 / 2009) Then
Me.AllowEdits = False
Me.AllowDeletions = False
Else
Me.AllowEdits = True
Me.AllowDeletions = True
End If

No error messages come up, but, can still edit

 

[boblarson]

That's because the syntax is wrong for a specific date:

It should be -

Private Sub Form_Current()
If Me![Date] < #7/1/2009# Then
   Me.AllowEdits = False
   Me.AllowDeletions = False
Else
   Me.AllowEdits = True
   Me.AllowDeletions = True
End If
End Sub

Also, if you have a field in your table that is named "DATE" then change it to something else. DATE is an Access Reserved Word so any objects or fields should not be named just DATE. You CAN use something like RecDate or DateOfHire but you can't use DATE.

 

[quinncooper1988]

SORTED!!!

THANKS A LOT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


It took me a while, but we got there.

Thanks a lot, you don't know how much your help means to me!!!

thank you

Q