Access 2000 form that needs data from dbase 3 and writes to table 1

[geekboi135]

I've been searching for solutions for a week now and I can't find any examples of this. I am just learning Access 2000 that my company works with as well as trying to learn more about VBA for Access 2000. I need to get a form working that has a combobox that takes clientnumber and clientname data from a dbase 3 database. I currently have it getting the data from a query that is linked to the dbase 3 database. My problem is with the text boxes I'm trying to get working that I want it to read and write to a memo field in the "ClientBible" Access 2000 table. Ideally if it could copy the clientname and clientnumber data from the dbase 3 to the ClientBible table and save the payrollmemo, employeememo, etc. that I have on a tabbed form to the table I have that I want to store the new data to. I need it designed to store the 4 memo fields attached to a client number and client name from existing clients from the dbase 3 database that we process our payroll on. Please help with what I will need to get this working. Thanks.

 

[Remou]

I am not sure what the problem is exactly. It has been some time, but I seem to recall that the import wizard will detect a .dbt file, if one exists, and ask what you want to do about it.

 

[geekboi135]

Sorry if I was confusing with the details. My problem is that the text boxes in the Access 2000 form are not linked to the memo fields on the table I have to store values. I can't find a way to link the fields. Most likely I'll need some code to direct it to the table manually since it has the name? error when I change it's ControlSource to the appropriate table. I'm new to Access and VBA so thanks for your patience.

 

[Remou]

My dBase III samples don't have memos. Can you upload a sample to an FTP site?

 

[geekboi135]

The memo fields I discussed is only memo type fields for the .mdb database table. The dbase 3 fields I need to get client data from are simple text I think. Our server crashed today and the drives are being rebuilt as we speak. I'll get more information about those particular fields when I can access the data. Thanks for your attention.

 

[Remou]

Ok. I think I have a clearer picture. You wish to transfer information from three dBase fields to a single Access memo field, is that correct? Do you wish to transfer the data all at once using an update query, or do you wish to, say, click a button and have the data added to the memo for the current record? These two are, I think, your main options.

 

[geekboi135]

Almost. The 2 (dbase3) fields I want transferred to 2 corresponding fields in the .mdb table (each in its own field on one row). Then, There are the memo fields that I want the form to input data to the corresponding memo field that is drawn as a text box on the form. Lastly I want all this data to be written to the .mdb table when the user presses the "Add Record" button I have on the form. To save it to the table. So the "Add Record" button will transfer the clientname and clientnumber from the dbase3 database to the clientname and clientnumber fields on the .mdb table. It will also write the employee memo, payroll memo, etc. fields to the same table in one row.

 

[Remou]

Have you set up any code for the Add Record button? I would imagine that an append query should suit.

 

[geekboi135]

The Add Record button does have some onClick event code that my supervisor added when he designed the form. I've just been given the task of finishing the form's code to get the form functional. I'll be happy to share the code once the server is restored if that will help. Which may or may not be today seeing that there is 2 hours left in my workday. I just know very little about VBA code. Its a small company so we have some time I can devote to learning.

 

[Remou]

Good luck with the server. See you later.

 

[geekboi135]

The server is now operational. The code that is in the Add Record button is as follows:
Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click


DoCmd.GoToRecord , , acNewRec

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

End Sub
I only understand a few of the commands. I just need to find out how to get the function out of it as described above. Thanks

 

[Remou]

Congratulations!

I think you can skip the bit about going to a new record, what you need is an insert query. You can build one in the query design window and post the sql back here, I will talk you through setting it up to run in code, it is quite easy.

 

[geekboi135]

For clarity sake I want to make sure we are thinking about the same thing. By an insert query do you mean an append query? They're probably the same thing I just want to be sure before I procede.

 

[Remou]

Yes, sorry, they start with Insert, well that's my excuse.

 

[geekboi135]

I chose the fields I want to insert from the form, now I have another question. I have 5 other combo boxes that select the day of the week of payroll actions, ie. Pay Period end, Check Date, etc. is there a way I can have the form automatically insert the day of the week from a date that is already written in the dBase III linked database? I remember seeing a function that will tell the day of the week from a specific date. On my append query I selected the fields that this code should update however the client number and client name fields should be inserted when selected from a combobox that lists all active clients I wasn't sure if I should add those. Here's the sql:

INSERT INTO [Client Bible] ( CLIENT_NO, Client, [Employee Memo], [Payroll Memo], [WC Memo], [Printing Memo] )
SELECT [Client Bible].CLIENT_NO, [Client Bible].Client, [Client Bible].[Employee Memo], [Client Bible].[Payroll Memo], [Client Bible].[WC Memo], [Client Bible].[Printing Memo]
FROM [Client Bible];

 

[Remou]

I hope you have a test database.

For a weekday name, you can use:
[tt]WeekdayName(Weekday(Date), , vbSunday)[/tt]

You will need to set the week start for your locale.

You can run your query as follows:

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click
Dim db As Database
Set db = CurrentDB

strSQL="INSERT INTO [Client Bible] ( CLIENT_NO, Client, " _ & "[Employee Memo], [Payroll Memo], [WC Memo], " _
& "[Printing Memo] ) " _
& "SELECT [Client Bible].CLIENT_NO, " _
& "[Client Bible].Client, " _
& "[Client Bible].[Employee Memo], " _
& "[Client Bible].[Payroll Memo], " _
& "[Client Bible].[WC Memo], " _
& "[Client Bible].[Printing Memo] " _
& "FROM [Client Bible]"

db.Execute strSQL, dbFailOnError

Exit_cmdAddRecord_Click:
    Exit Sub

Err_cmdAddRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddRecord_Click
    
End Sub