Add a record to a table from a form (1 Viewer)

paulvetter

New member
Local time
Today, 01:52
Joined
Nov 4, 2013
Messages
8
HI, I have limited experience in Access, but I have managed to create a user form, where items are selected from comboboxes (Test results; when, who, outcome, etc)
At the end I have built in a command button that should add a record to an existing table, but so far no luck. I have used the following VB code for this:

Private Sub Save_test_results_KeyPress(KeyAscii As Integer)
Dim dbsICT_Test_Management As DAO.Database
Dim rstActual_test_results As DAO.Recordset

Set dbsICT_Test_Management = CurrentDb
Set rstActual_test_results = dbsICT_Test_Management.OpenRecordset("Actual_test_results")

rstActual_test_results.AddNew
rstActual_test_results.Update

End Sub

Anybody have an idea?

Thanx
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:52
Joined
Aug 30, 2003
Messages
36,118
Well, you haven't told it what to add. The simplest solution is to bind the form to the table (the form's record source property) and textboxes to the fields (their control source property). That lets Access do all the work for you.

If you want to stay with what you're doing, you have to do all the work:

rstActual_test_results.AddNew
rstActual_test_results!FirstFieldName = ValueFromFormHere
rstActual_test_results!SecondFieldName = ValueFromFormHere
rstActual_test_results.Update

Plus the way you've opened the recordset will bog down if you get a lot of records.
 

paulvetter

New member
Local time
Today, 01:52
Joined
Nov 4, 2013
Messages
8
OK, I'll give that a try.

Thanx again!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:52
Joined
Aug 30, 2003
Messages
36,118
No problem, post back if you get stuck.
 

paulvetter

New member
Local time
Today, 01:52
Joined
Nov 4, 2013
Messages
8
Hi Paul,

I've been looking for the form's record source property, but can't find it. The only properties showing is whether it's visible, color, height, etc. Mind, this is a 2007 version. So I'll proceed with the VB code, and see if that works out. I've also tried to bind the comboboxes to the relevant actual test result fields, but then they are not selectable from the combo any more. I'll let you know....

Cheers for now!
 

paulvetter

New member
Local time
Today, 01:52
Joined
Nov 4, 2013
Messages
8
OK Paul,

I'm apparently making a VB error when trying to write to the Actual_test_result table:

rstActual_test_results.AddNew
rstActual_test_results!Type_of_test = ValueFromForm_TRF-1!(Combo24)

Set rstType_of_test = Form_TRF-1!Type_of_test.RecordsetClone

Both methods end in a dialogue box telling me it's expecting an end of statement, while I'm using the second method straight from 2007HelpInfo. What am I doing wrong here?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:52
Joined
Aug 30, 2003
Messages
36,118
It doesn't sound like you're viewing the form's properties, as one of the properties on the data tab would be Record Source. It sounds like you're on the detail section or something. At the top of the properties window it will tell you what you're viewing the properties of.

If you decide to go the other way, your form reference is off. You'd replace "ValueFromFormHere" with the appropriate reference:

http://www.mvps.org/access/forms/frm0031.htm
 

Users who are viewing this thread

Top Bottom