Adding Records

  • Thread starter Thread starter Guest
  • Start date Start date
Could you explain a little more abut what you want to do?

A table that isn't opened could mean a lot of things.
The database isn't open
The table is in another database and is not a linked table
You have a form open and it doesn't have the table in its record source
You have a query open and the table is not included in the tables used by
the query
....



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
The table is in the same database that I have a form opened in. No the form
is not based on the table.

I know that there has to be a way in VB to open the table and add a record,
I just don't know how to do it.
 
I'm sorry. I'm being dense. I still don't understand what you want to do.

Anyone else care to step in?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I have a form that is not based on the table that I want to add a record to.
Isn't there a way in VB to open a table, append a record to it, then close
the table?
 
You can use VBA to execute an append query. You can build the append
query string in VBA.

If it is one record based on some values that you know then the SQL might be

StrSQL = "INSERT INTO [TheTable] " & _
"([numberFieldA], [textFieldB], [DateFieldC])" & _
" Values (" & intSomeNumber & _
", """ & strSomeText & """, #" & _
txtSomeDateValue & "#)"

CurrentDB().Execute StrSQL

THat is the best I can do without a lot more detail.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Denise,

Let's try next.

I assume you have txtboxes at your form where dta was entered!
Give these textboxes a decent name (select box, then in properties: tab
"other", field "Name") like "txtStartdate" for a startdate or "txtAmount" for
a number of items. Give it a logicalname.
Then create a button on your form to save the data.
Click on the right mousebutton while selecting the new button and select
"Build Event" to go the codepage.
the code to write ,looks like following, depending of the fields in your
table you need to add and add the real table name at "TblName".


Private Sub pbSaveRecord_Click()
Dim rsDestinationTable As Recordset

Set rsDestinationTable = CurrentDb.OpenRecordset("TblName", dbOpenTable,
dbFailOnError)

With rsDestinationTable
.AddNew
.txtStartdate = Me.txtStartdate
.txtAmount = Me.txtAmount
.Update
End With

End Sub

Goodluck,
Martin

John Spencer said:
You can use VBA to execute an append query. You can build the append
query string in VBA.

If it is one record based on some values that you know then the SQL might be

StrSQL = "INSERT INTO [TheTable] " & _
"([numberFieldA], [textFieldB], [DateFieldC])" & _
" Values (" & intSomeNumber & _
", """ & strSomeText & """, #" & _
txtSomeDateValue & "#)"

CurrentDB().Execute StrSQL

THat is the best I can do without a lot more detail.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a form that is not based on the table that I want to add a record to.
Isn't there a way in VB to open a table, append a record to it, then close
the table?
Click to expand...
Click to expand...
 
Back
Top