How to Run 2 Append Queries and Clear datasheet from 1 macro

[Daina]

Hi,

I have two identical databases 1 is the main database and the other is loaded onto a laptop for field officers to gather information, at the end of the day I want the field officers to be able to click on 1 button on the laptop which will append the data from the 2 tables on the laptop onto the same 2 tables on the main database.

I already have the append queries on the laptop to perform this function, what I need help with is:
To use a macro that will run both append queries, and clear the datasheet ready for the next field trip. Does anyone know whether this can be done? and how?

Cheers

Dee

 

[jbehrne]

Hi,

You are better off just running some vb code to do the job. It is alot easier to understand than it looks. Your button's "On Click" event runs the queries's sql statements - and I have an example one that will delete the info from the laptop tables. It would look something like this:

***********************************************************
'Turn off warning messages
DoCmd.SetWarnings False

'Run the first append sql statement - you can get this by
'viewing your append query in design mode then go to
'view - sql view
DoCmd.RunSQL "INSERT INTO Table1 ( Field ) SELECT tblLapTop.Field FROM tblLapTop"

'run the sql statement to clear the tables on the laptop
'access database - this is just a delete query
DoCmd.RunSQL "DELETE tblLapTop.Field FROM tblLapTop"

'turn our warnings back on
DoCmd.SetWarnings True
***********************************************************

However, if you are dead set on using a macro then it would look something like this:
- The items in parentheses are particular settings for each selection in your macro

Set Warnings
(Warnings On: No)
OpenQuery
(Query Name: AppendQuery)
(View: Pivot Table - or whatever from list)
(Data Mode: Read Only)
Close
(Object Type: Query)
(Object Name: AppendQuery)
(Save: Yes)
Set Warnings
(Warnings On: Yes)

- as you can see the macro does the same thing, but the vb code is faster to run (and in my opinion better)

HTH,

jbehrne
If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[Daina]

jbehrne

Thanks I will try both of these methods, and let you know.


Cheers

Dee

 

[Daina]

jbehrne

UI am sorry but the vb code totally confused me so I tried the macro but I think I got the sql statement wrong as this is whrere the macro stops with an error, I have laid out exactly as in my db please let me know if you can see the error.

Set Warnings
Warnings on - No

Open Query
Query Name - QryAppendPersonalDetails
View - Datasheet
DataMode - Read Only

Close
Object Type - Query
Object Name - QryAppendPersonalDetails
Save - No

Open Query
Query Name - QryAppendVaccinations
View - Datasheet
DataMode - Read Only

Close
Object Type - Query
Object Name - QryAppendVaccinations
Save - No

Open Table
Table Name - tblPersonalDetails
View - Datasheet
Data Mode - Edit

RunSQL
SQLStatement - DELETE FROM tblPersonalDetails WHERE Details ID=*;
Use Transaction - No

Close
Object Type - Table
Object Name - tblPersonalDetails
Save - Yes

Open Table
Table Name - TblVaccinations
View - Datasheet
Data Mode - Edit

RunSQL
SQLStatement - DELETE FROM TblVaccinations WHERE Immunisation ID=*;
Use Transaction - No

Close
Object Type - Table
Object Name - TblVaccinations
Save - Yes


Thank you

Dee

 

[jbehrne]

Hi Dee,

Could you post the error message that you received? I will also need to see your sql statements from your queries. One last quick question... does your data actually transfer between the two databases?

jbehrne If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[Daina]

jbehrne

This is the message that appears:

Action Failed

Macro Name:
McrAppendClearDatasheet
Condition:
True
Action Name:
RunSQL
Arguments:
DELETE FROM tblPersonalDetails WHERE Details ID=*;

Here is the SQL Statement from the QryAppendPersonalDetails

INSERT INTO tblPersonalDetails ( [Date of Visit], [Medicare No], [Second Medicare No], Surname, [Second Surname], [First Name], [Parent First Name], [Parent Last Name], [Date of Birth], Sex, Address, Suburb, Postcode, [BH Telephone], [AH Telephone], [Risks & Benefits Discussed], [Consent Obtained] ) IN 'W:\Datasets\PHU Databases\Immunisation Records.mdb'
SELECT tblPersonalDetails.[Date of Visit], tblPersonalDetails.[Medicare No], tblPersonalDetails.[Second Medicare No], tblPersonalDetails.Surname, tblPersonalDetails.[Second Surname], tblPersonalDetails.[First Name], tblPersonalDetails.[Parent First Name], tblPersonalDetails.[Parent Last Name], tblPersonalDetails.[Date of Birth], tblPersonalDetails.Sex, tblPersonalDetails.Address, tblPersonalDetails.Suburb, tblPersonalDetails.Postcode, tblPersonalDetails.[BH Telephone], tblPersonalDetails.[AH Telephone], tblPersonalDetails.[Risks & Benefits Discussed], tblPersonalDetails.[Consent Obtained]
FROM tblPersonalDetails INNER JOIN TblVaccinactions ON tblPersonalDetails.[Medicare No] = TblVaccinactions.[Medicare No];

Here is the SQL Statment from the QryAppendVaccinations

INSERT INTO TblVaccinactions ( [Date], [Medicare No], Centre, Vaccine, [Batch No], Immuniser ) IN 'W:\Datasets\PHU Databases\Immunisation Records.mdb'
SELECT TblVaccinactions.Date, TblVaccinactions.[Medicare No], TblVaccinactions.Centre, TblVaccinactions.Vaccine, TblVaccinactions.[Batch No], TblVaccinactions.Immuniser
FROM tblPersonalDetails INNER JOIN TblVaccinactions ON tblPersonalDetails.[Medicare No] = TblVaccinactions.[Medicare No];

No data transfers between the 2 databases, hope this helps

Again thank you

Dee

 

[jbehrne]

Hi,

Your delete query should look like this: (without quotes)
- This one will just delete everything from the table
"DELETE * FROM tblPersonalDetails;"

If you want it delete only certain record than it would look like this:

"DELETE * FROM tblPersonalDetails WHERE Details.ID = YOUR CONDITIONS;"
- I am assuming that details is your table and ID is your field. "Your Conditions" would be the critieria that you would limit your data by... You are getting the error because you are not deleting anything and you probably don't have any IDs equal to '*'. Try the queries and see if that works,

jbehrne

- i will whip out some vb code for you tomorrow at work that runs your queries If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations