Sample Code to Accept form data, then write it to a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Would anyone be able to point me to a simple example of how to open a form,
accept the data in the form, open a table, and write the data to a row in the
table (using VBA coding). I know Access will do all this for me, but I have
a much more complicated need than that, but if I see how it is done, I can
take it from there.

Help!

Pat K
 
PatK,

What exactly are you trying to do? What are your needs? If you use a bound
form and bound controls, then a valid value you type into a control will be
written to the table.

To open a form, you'd use DoCmd.OpenForm "YourFormName"

Have no idea what you mean by 'accept the data'. Where is the data coming
from?

Brian
 
Hey...thanks for the reply. Here is more info...sorry about that.

The input data is being entered in an unbound form field or two. How it
works when the app runs is:
- They open the form (I am just doing that in access right now)
- A window pops up and accepts two field values
- they hit the "generate" button
- I then open a DB....read in a series of records....write each retrieved
row of info into "another" table, with the two entered data fields prepended
onto each row. Thus:
Popup form
enter data in two fields
hit generate button
open tbl X
Read a row
move two fields of data plus row into a new table row
repeat until eof on input table.

Getting that two fields of data from the form, to my subroutine, is what I
am trying to accomplish....I know for you guys, this is incredibly easy, but
boy is it hard to find out how to do easy things :-) Hard things, no
problem! :-) So, thanks for not laughing at the simple question, and ANY
response you can provide.

Pat
 
Pat,

you've perplexed me with the following:
- They open the form (I am just doing that in access right now)
Click to expand...

Are you not wanting to do this in Access?

Brian
 
PatK said:
Would anyone be able to point me to a simple example of how to open a form,
accept the data in the form, open a table, and write the data to a row in the
table (using VBA coding). I know Access will do all this for me, but I have
a much more complicated need than that, but if I see how it is done, I can
take it from there.

Help!

Pat K
Click to expand...

Pat,

Here's a function that adds a record to a table. This uses the ADO versus
AOD. Make sure your references are correctly set.

Public Function AddRecordToTable(strTableName As String, strField1 As
String, strField2 As String)

Dim dbs As ADODB.Connection, rst As ADODB.Recordset
Set dbs = CurrentProject.Connection ' connect to current database
Set rst = New Recordset ' create a recordset

rst.Open strTableName , dbs, adOpenDynamic, adLockOptimistic ' open
table
' you opened adOpenDynamic because you want to add or modify records
'use adOpenStatic to just read the table

rst.AddNew ' adds a record
rst!Field1Name = strField1 ' sets first field
rst!Field2Name = strField2 ' sets second field
rst.Update ' saves the new record

' this is actually three commands in one line,
' it closes table and disconnects from DB
rst.Close: Set rst= Nothing : Set dbs = Nothing

End Function
 
Back
Top