Appending to other database based on status of column (1 Viewer)

Minty

AWF VIP
Local time
Today, 21:55
Joined
Jul 26, 2013
Messages
10,355
Hi Minty, thanks for your help.. but I dont know how to display information in read only form.. Can you please help me step by step ?

Thanks
DD
In the forms properties set the recordset type to Snapshot - this is an non-editable view of your data.
 

DevAccess

Registered User.
Local time
Today, 14:55
Joined
Jun 27, 2016
Messages
321
Ok, I will give a try in few hours and let you know if I need anything from you, thanks..
 

DevAccess

Registered User.
Local time
Today, 14:55
Joined
Jun 27, 2016
Messages
321
Hi, thanks for your help I will give this a try.

Hi, below gives syntax error. I am running below code in Query Design --> SQL query design and directly pesting there.


INSERT INTO [C:\ydb.accdb].tbl_backuprecords
SELECT * from tbl_MasterRecords T
WHERE [CURRENT STATUS]='NEW'
AND NOT EXISTS
(SELECT NAME FROM [C:\ydb.accdb].tbl_backuprecords
WHERE T2.CURRENT STATUS = 'NEW' AND T.SSN=T2.SSN)
 

DevAccess

Registered User.
Local time
Today, 14:55
Joined
Jun 27, 2016
Messages
321
Hi Minty, thanks for your help.. but I dont know how to display information in read only form.. Can you please help me step by step ?

Thanks
DD

I set form recordset =SELECT *FROM tbl_MasterRecords WHERE [STATUS]='NEW'
and recordsettype= snapshot, but it gives blank form instead of report.
 

Minty

AWF VIP
Local time
Today, 21:55
Joined
Jul 26, 2013
Messages
10,355
I set form recordset =SELECT *FROM tbl_MasterRecords WHERE [STATUS]='NEW'
and recordsettype= snapshot, but it gives blank form instead of report.

If you click the expression builder on the recordset property and go to the query view does it bring up any records in the query ?

It not then your query is wrong. Use the query builder to get that bit correct then you should see some records.

And a form is a form but could be used to display a report if that's what you want.
 

DevAccess

Registered User.
Local time
Today, 14:55
Joined
Jun 27, 2016
Messages
321
If you click the expression builder on the recordset property and go to the query view does it bring up any records in the query ?

It not then your query is wrong. Use the query builder to get that bit correct then you should see some records.

And a form is a form but could be used to display a report if that's what you want.

In form it is blank but i can see 1 of 39 records in statusbar at bottom

I ran query into query designer and it gives me 39 records. but form is blank,

Do I need place any fields on the form?? also how does this solve the purpose of backup database, I actually I want to take backup master table of X database and see if status ="DPT" then put into Y databse. also this should not put duplicate entery everytime the query runs.
 

DevAccess

Registered User.
Local time
Today, 14:55
Joined
Jun 27, 2016
Messages
321
this code worked in sql query view in access, but anyone can help me how to automate like autoexec macro specialy how to refer this query of access in autoexec macro.?

Code:
INSERT INTO tbl_backupDPTrecords
SELECT * FROM tbl_MasterRecords
WHERE Left(tbl_MasterRecords.[STATUS],3) like "*NEW*"
AND NOT EXISTS (SELECT [SSN] FROM tbl_backupDPTrecords
WHERE Left(tbl_backupDPTrecords.[STATUS],3) LIKE '*NEW*' AND tbl_MasterRecords.[SSN]=tbl_backupDPTrecords.[SSN]);
 

sneuberg

AWF VIP
Local time
Today, 14:55
Joined
Oct 17, 2014
Messages
3,506
I believe you can just use the OpenQuery command. You should find your query name in the drop down. The follow is a macro where I'm running a make table query named "Query1"


 

Attachments

  • AutoExec Macro Screen Shot.jpg
    AutoExec Macro Screen Shot.jpg
    25.4 KB · Views: 168

Users who are viewing this thread

Top Bottom