Adding records to a table from an unbound form 2

[TerryEA]

Hi

I'm trying to do something that I guess should be simple.

I want the user to type information into a few fields, on an unbound form, and then click on a "POST" command button which adds 2 new records to a table automatically, using the data entered (One new record will do for now). I don't want the table to be seen.

As a trial, I have the following code, in the OnClick event of the command button, simply entering text ("CT") into one of the fields, and then hopefully adding a record in the table, , but nothing happens. Have I coded something wrongly or am I approaching this the wrong way? Do I have the worry about the exact name of the database?

Would appreciate expert help.

Terry
Code:

Private Sub cmdSAVETRANS_Click()

Dim dbEA As DAO.Database
Dim rcdTRANS As DAO.Recordset
Set dbEA = CurrentDb
Set rcdTRANS = dbEA.OpenRecordSet("tblTRANSACTIONS")
rcdTRANS.AddNew
rcdTRANS![TRTYPE] = "CT"
rcdTRANS.Update

End Sub

 

[pdldavis]

This might work for you:

Dim tType As String

tType = Me.txtType 'text box on form

DoCmd.RunSQL "INSERT INTO tblTRANSACTIONS ( TRTYPE )SELECT '" & tType & "'"

Hope that helps.

 

[TerryEA]

Hi pdldavis

That seems OK but how would I use that method if I want to put text into say 6 fields (not just the [TRTYPE] field) before adding the record to the table?

Terry

 

[Remou]

TerryEA
I cannot see any reason why your sample code should have failed, it worked for me in a mock-up. It may be worth stepping through to see what is happening.

 

[TerryEA]

Hi Remou

Neither can I! The table I am trying to write to currently has zero records in it. Could that have any bearing?

What do you mean by "stepping through"?

btw, the table also has an autonumber field, although I don't use it for anything.

Terry

 

[Remou]

Type Stop at the start of the code, this will make the code break and allow you to press F8 to step to the next line. Keep pressing F8, to test each line.

 

[TerryEA]

Remou

I tried the "STOP" and F8 but got nowhere so I used the msgbox function between everyline to check where the code was stopping. It doesn't even get past the first line:

Dim dbEA As DAO.Database

Can't see anything wrong with it, can you?

If I use the apostrophe before each line (comment) the code goes through to the last msgbox and completes. I'm confused.

Terry

 

[Remou]

Have you a reference to the Microsoft DAO 3.x Object Library?

 

[TerryEA]

Remou

Hadn't thought of that. I went to check the refernces on the tools menu and Access then hung up. After that I couldn't find References again. I'm restarting now so will get back to you in a minute.

Terry

 

[TerryEA]

Remou

References are no longer on the Tools Menu. Any ideas where else I can access them?

Terry

 

[Remou]

There are not on the ordinary tools menu, they are on the code window tools menu (Alt + F11).

 

[TerryEA]

Remou

OK. I've added the Microsoft DAO 3.6 Object Library to the references and stepped through the code. It runs to the end and completes but it still hasn't added a record to the tblTRANSACTIONS table. So making progress, but not yet there.

Terry

 

[TerryEA]

Remou

please take a look at the code for me now. I've added a msgbox between each line. It seems that the code will run up to message6 but I think the last line doesn't run because the last message (after the line: rcdTRANS.Update) doesn't display.

code:

Private Sub Command17_Click()

MsgBox "Message 0"
Dim dbEA As DAO.Database
MsgBox "Message 1"
Dim rcdTRANS As DAO.Recordset
MsgBox "Message 2"
Set dbEA = CurrentDb
MsgBox "Message 3"
Set rcdTRANS = dbEA.OpenRecordSet("tblTRANSACTIONS")
MsgBox "Message 4"
rcdTRANS.AddNew
MsgBox "Message 5"
rcdTRANS![TRTYPE] = "CT"
MsgBox "Message 6"
rcdTRANS.Update

MsgBox "Complete"
End Sub

 

[TerryEA]

Remou

It's working. The problem was: required fields in the table. Because I hadn't got as far as completing all the required fields it wouldn't add the record.

Many thanks for your help - I wouldn't have got there without you. That's the second time references have caught me out.

Thanks again.

Terry