trying to add data from a form to a table

[RyGuy]

Using Access 2007. I have a reference to the following:
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft ActiveX data Objects 2.8 Library.

I’m trying to add data from a form to a table. I’ve been experimenting with
a few versions of this code for a few days now. Still struggling.

Private Sub Post_Click()
Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String
'Dim mydb As DAO.Database
Dim mydb As String

'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\Pivot Trading System.accdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
'On Error GoTo S:
cnn1.Open strCnn

' Open contact table.
Set rstcontact = New ADODB.Recordset
rstcontact.CursorType = adOpenKeyset
rstcontact.LockType = adLockOptimistic
rstcontact.Open "Options", cnn1, , , adCmdTable

'get the new record data
rstcontact.AddNew
'rstcontact!OptionsNo = OptionsNo
rstcontact!TicketNo = TicketNo
rstcontact!Side = Side
rstcontact!Symbol = Symbol
rstcontact!Quantity = Quantity
rstcontact!Strike = Strike
rstcontact!Call_Put = Call_Put
rstcontact!Price = Price
rstcontact!Exchange = Exchange
rstcontact!Approved = Approved
rstcontact!DateAdd = Now()
rstcontact.Update
' Show the newly added data.
MsgBox "New Post: " & rstcontact!TicketNo & " " & rstcontact!Symbol & " has
been successfully added"
'close connections
rstcontact.Close

Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub

Code errors here:
cnn1.Open strCnn

Error message is:
Unrecognized database format ‘C:\Pivot Trading System.accdb’

I never had any errors with this code in Access 2003. Now, in 2007, nothing
but errors.

Any ideas?

Thanks!
Ryan---

 

[David C. Holley]

If the FORM is BOUND to the table and the CONTROLS are bound, YOU DO NOT
NEED TO HANDLE INSERTING THE DATA. Access will handle that itself. At this
point I would *highly* recommend that you pick up a book about Access as
this is a very basic concept.

If you have any questions, please refer back to the your original post a few
days ago.

 

[ryguy7272]

Yes, the fields are bound to the table. I think this is going to require an
Update Query. The thing is this...I have two sub-forms embedded in a form,
and you can toggle back and forth between these two sub-forms by clicking the
tabs at the top (insert page & delete page). One sub-form is called
'Options' and the other sub-form is called 'Stocks'. So what I want to do,
ultimately, is make sure all fields on both sub-forms are filled in, and then
click a button to send the data from BOTH tabs to the respective
tables...probably with an Update Query.

I have created a query that combines the records from both tables. Should I
link the form to this new query? I have 100 records in the Options table and
25 in the Stocks table (I created a join where all records from the Options
table AND all records from the Stocks table are included – query has 100
records displayed). I'm not sure how to tell Access to update the correct
records!! There seems to be a major disconnect between what I'm trying to do
and what I'm actually able to do here!!

I've worked with Update Queries quite a bit in the past, but it was just a
Query updating records in a table, and all I did was call the Update Query
with some simple VBA. I have never developed a form to control an Update
Query to pass records from the form to a table.

Are there are good resources online that describe how to do this? I've read
many Access books and don't recall every reading anything about this.
Nevertheless, I think it can be done. I know these discussion groups are
great for learning things wayyy beyond what you will ever learn from a book,
because no book can possibly cover every topic.

Thanks for everything!
Ryan—

 

[David C. Holley]

Can you post the recordsource's for each subform as well as the main form?
The SQL statement displayed in the .RecordSource property field for the form
and each of the sub forms.

 

[ryguy7272]

I don't understand. What do you mean recordsource? Under ControlSource each
TextBox is bound to the table. I see properties there, but I don't know how
to get all .RecordSource property fields. Is there some kind of macro, or
VBA, to get all .RecordSource property fields?

Thsanks for everything!
Ryan--

 

[John W. Vinson]

I don't understand. What do you mean recordsource? Under ControlSource each
TextBox is bound to the table. I see properties there, but I don't know how
to get all .RecordSource property fields. Is there some kind of macro, or
VBA, to get all .RecordSource property fields?

The *FORM ITSELF* has properties (not just the controls on the form).

To see them click the little square at the upper left intersection of the
rulers and select Properties. The Recordsource of the form is the name of the
table, of the query, or the SQL string of an ad-hoc query which provides the
data for the form.

 

[ryguy7272]

Ah! Now it's starting to make sense. Here is the Record Source, which is
the SQL:
SELECT Trades.CUSTOMER, Trades.ContraTrader, Trades.[Entered By],
Trades.[Open/Close], Trades.RR, Trades.Broker_Solicit_From, Trades.[Solicited
From], Trades.ExecBroker, Trades.OrderType, Trades.RateBi_Pass,
Trades.Adjusted_Cust_Rate, Trades.FeeBi_Pass, Trades.Adjusted_Brk_Fee,
Trades.Notes, Trades.Post, Trades.TradeDate, Trades.TicketNo
FROM Trades;

 

[John W. Vinson]

Ah! Now it's starting to make sense. Here is the Record Source, which is
the SQL:
SELECT Trades.CUSTOMER, Trades.ContraTrader, Trades.[Entered By],
Trades.[Open/Close], Trades.RR, Trades.Broker_Solicit_From, Trades.[Solicited
From], Trades.ExecBroker, Trades.OrderType, Trades.RateBi_Pass,
Trades.Adjusted_Cust_Rate, Trades.FeeBi_Pass, Trades.Adjusted_Brk_Fee,
Trades.Notes, Trades.Post, Trades.TradeDate, Trades.TicketNo
FROM Trades;

If this is the recordsource for the subform then...

Entering data into any of the controls bound to these fields and doing
anything to move off the subform (moving to the mainform, to the other
subform, closing the form, hitting Shift-Enter, etc.) will automatically and
silently write those values into the table Trades.

No code is needed.

No query is needed.

No Append is needed.

That's what bound forms are FOR - you type into the form, Access stores it in
the table for you. No muss, no fuss!

 

[ryguy7272]

Sorry for being so thick John!! You are totally right. I've never done it
that way before. My experience with forms is somewhat limited. In the past,
I have used Access mostly for queries (and then exporting results to Excel)
and reports.

Thanks for showing me how this feature works!!

Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Ah! Now it's starting to make sense. Here is the Record Source, which is
the SQL:
SELECT Trades.CUSTOMER, Trades.ContraTrader, Trades.[Entered By],
Trades.[Open/Close], Trades.RR, Trades.Broker_Solicit_From, Trades.[Solicited
From], Trades.ExecBroker, Trades.OrderType, Trades.RateBi_Pass,
Trades.Adjusted_Cust_Rate, Trades.FeeBi_Pass, Trades.Adjusted_Brk_Fee,
Trades.Notes, Trades.Post, Trades.TradeDate, Trades.TicketNo
FROM Trades;

If this is the recordsource for the subform then...

Entering data into any of the controls bound to these fields and doing
anything to move off the subform (moving to the mainform, to the other
subform, closing the form, hitting Shift-Enter, etc.) will automatically and
silently write those values into the table Trades.

No code is needed.

No query is needed.

No Append is needed.

That's what bound forms are FOR - you type into the form, Access stores it in
the table for you. No muss, no fuss!