Transer data from Access to MYOB

[ontherun]

Hi,

I have designed a database to enter Jobs for each client and is working good.

Now i am asked to transfer the Job information to MYOB to generate an Invoice.

I dunno how to do this... first of all, is this possible ?
if not, please provide any other suggestion.

thanks in advance.

Kathy

[MMcCarthy]

Hi,

I have designed a database to enter Jobs for each client and is working good.

Now i am asked to transfer the Job information to MYOB to generate an Invoice.

I dunno how to do this... first of all, is this possible ?
if not, please provide any other suggestion.

thanks in advance.

Kathy

Hi Kathy

I'm not familiar with MYOB but you need to find out what kind of file it will accept. Chances are it will look for either a fixed length text file or a csv file either of which you can dump from the Access database.

Mary

[ontherun]

thanks mccarthy.

yeah i checked with MYOB and it seems it accepts txt file.
i just need to tranfer the fields related to invoice from Access.

Still trying to figure out some solution.. the accountant of my company wanted a reliable way to do this, so that she can get the required data frm access , whenever needed, to generate an invoice in MYOB.

will appreciate any help in the mean time.

thanks n regards,

Kathy

[MMcCarthy]

Design a query with the relevant fields. Then put this code behind a command button on a form.

DoCmd.TransferText acExportDelim, "Specification Name", "Query Name", "File to be created", True

acExportDelim - This will output a text file with the values separated by commas
"Specification Name" - Run the export manually and the export wizard will open. Click on the advanced tab and save the settings for the export as a specification.
"Query Name" - the name of the query you created with the required data
"File to be created" - the full path and name of the text file to be created
True - Set to true if you want the field names to be outputted and false if you don't

[ontherun]

hi mccarthy,

nice 2 see ur reply..

i have created a query with relevant fields.

regarding the specification name, u have asked me to open the export wizard.

i clicked Export from File Menu but it asks for the file to export. i think i might pressed the wrong one... could you please tell, whether this is right or how to export manually... sorry i know am asking like an amatuer...

need your help...

thanks,

Kathy

[MMcCarthy]

hi mccarthy,

nice 2 see ur reply..

i have created a query with relevant fields.

regarding the specification name, u have asked me to open the export wizard.

i clicked Export from File Menu but it asks for the file to export. i think i might pressed the wrong one... could you please tell, whether this is right or how to export manually... sorry i know am asking like an amatuer...

need your help...

thanks,

Kathy

That's OK Kathy

When you hit Export the file save window will open asking for the name of the file you want to export to as normal. If you change the file type to text and enter the new filename then click save. The export text wizard will then open.

Mary

[ontherun]

hi,
i tried to export the query straight away without using the code.
it opens a text file wheni gave the type as '.txt'

it works good. could you please assist me on how to fill in the code..

Query name : Generate_Invoice

should i need to create a seperate file whenever i try 2 use it 2 generate the invoice

thanks,
Kathy

[MMcCarthy]

hi,
i tried to export the query straight away without using the code.
it opens a text file wheni gave the type as '.txt'

it works good. could you please assist me on how to fill in the code..

Query name : Generate_Invoice

should i need to create a seperate file whenever i try 2 use it 2 generate the invoice

thanks,
Kathy

Ideally yes we can add a datestamp to the file name to distinguish it.

What is the name of the saved specification?

[ontherun]

Ideally yes we can add a datestamp to the file name to distinguish it.

What is the name of the saved specification?

i created the txt file 'Invoice.txt' using the query.... when i tried 2 export the file i cudnt get to the advanced set up which u explained.

Qery - Generate_Invoice
File Menu----------> Export
Save type -----------> txt
File Name -------------->Invoice.txt


[could you please tell me, where i can get the advanced option to name the specification?]


regards,
Kathy

[ontherun]

Hi,

I have found from one of your thread, that access has the sound feature to tigger some action.

Akin to my database, i want a pop-up menu with sound or something like that, to display a message "Record has been modified.Please inform the Accountant, if there is change in Total" whenever the user modifies or edit a particulat job record from a job form.

i wanna display this messange in "Job" Form.


Also, i need to display the last invoice number with respect to Client Number. I have written a search query to identify that with respect to the given invoice number but when i use navigation button to move to the next record, the last Invoice number is remain the same (even for different invoice number)

need your help.

thanks in advance.

Kathy

[MMcCarthy]

i created the txt file 'Invoice.txt' using the query.... when i tried 2 export the file i cudnt get to the advanced set up which u explained.

Qery - Generate_Invoice
File Menu----------> Export
Save type -----------> txt
File Name -------------->Invoice.txt


[could you please tell me, where i can get the advanced option to name the specification?]


regards,
Kathy

Select the query and right click to get Export.
Select file type as "Text File" and insert file name
Click the Export Button.
The Export Text Wizard should now open.

[ontherun]

Select the query and right click to get Export.
Select file type as "Text File" and insert file name
Click the Export Button.
The Export Text Wizard should now open.

Thanks for the reply...
i did the same but i got a message saying "too few parameters. Expected 1"when i clicked Export button

what is that mean?? is there any wrong in the query???
but when i run the query i got the result i wanted....

[MMcCarthy]

Thanks for the reply...
i did the same but i got a message saying "too few parameters. Expected 1"when i clicked Export button

what is that mean?? is there any wrong in the query???
but when i run the query i got the result i wanted....

This would normally indicate an error with the query. Are you sure it's running OK? Post the SQL of the query.

[ontherun]

This would normally indicate an error with the query. Are you sure it's running OK? Post the SQL of the query.

SELECT Job.Job_No, Job.Cli_ABN, Job.Purchase_Order_No, Job.Date_Enacted, Job.Date_Delivered, Job.Sales_person, Job.Job_Desc, Job.Qty, Job.Street_Address1, Job.Street_Address2, Job.Suburb, Job.Postcode, Job.State, Job.Job_Sub_Total, Job.GST, Job.Job_Total
FROM Job
WHERE (((Job.Job_No)=[Enter the Job Number:]));


i have created this using query wizard. When i type the job number, i get the results of the relavant fields.. this is just the testing but the fields will be changed depends on the input from the accountant.

I am in very much need of ur advice

Thanks,
Kathy

[MMcCarthy]

OK Kathy

The simple answer to your problem is you can't export a query where the query requires user input. As this will not be used in the final query then remove it and export the full query to get the specification sorted.

Mary