Updating a table with data from an unbound form via VBA In Access 2003 / 2007

[Pauline123]

HI, I am stuck - I would like to update a table with data from a form.
I initially used a bound form and placed a subform in a tab control on the main form but this failed to work.

Have now considering using a pop-up unbound form with a command button when clicked with update the table.

I found this code but am having trouble getting it go.
My table is = ChecklistDetailsTB
My unbound form is = ChecklistUpdateForm
with fields: txtRegistration / txtWE / txtDriver / txtMileageTD

Hope someone can help Pauline

Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
'Check that all fields are filled in
txtRegistration.SetFocus
If txtRegistration.Text = "" Then
err = err + 1
MsgBox "Please select the Registration!" & err
End If
txtWE.SetFocus
If txtWE.Text = "" Then
err = err + 1
MsgBox "Please fill in the the WE Date!"
End If
txtDriver.SetFocus
If txtDriver.Text = "" Then
err = err + 1
MsgBox "Please fill in Driver's Name!"
End If
txtMileageTD.SetFocus
If txtMileageTD.Text = "" Then
err = err + 1
MsgBox "Please fill in the current mileage!"

End If

'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\contacts.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

' Open contact table.
Set rstVehicleCheclists = New ADODB.Recordset
rstVehicleCheclists.CursorType = adOpenKeyset
rstVehicleCheclistsB.LockType = adLockOptimistic
rstVehicleCheclists.Open "ChecklistDetailsTB", cnn1, , , adCmdTable

'get the new record data
rstcontact.AddNew
rstVehicleCheclists!Registration = txtRegistration
rstVehicleCheclists!WE = txtWE
rstVehicleCheclists!Driver = txtDriver
rstVehicleCheclists!MileageTD = txtMileageTD
rstVehicleCheclists!Actioned = txtActioned
rstVehicleCheclists.Update
' Show the newly added data.
MsgBox "New contact: " & rstVehicleCheclists!Registration & " has been successfully added"

'close connections
rstcontact.Close
cnn1.Close

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

 

[Peter Reid]

To clarify, you're trying to update a table in the (external) database 'c:\contacts.mdb'?

 

[sneuberg]

HI, I am stuck - I would like to update a table with data from a form.
I initially used a bound form and placed a subform in a tab control on the main form but this failed to work.

I suggest retreating to that point and posting the problem you were having. I wouldn't go with an unbound form until I exhausted every possibility of using a bound form.

 

[Pauline123]

Hi Peter, sorry no I am trying to update a record in the current database but the coding refers to an external database and I am not experienced enough to amend the code.

The idea is to enter the details from Vehicle Checklists to the underlying table and then for the results to appear in a subform on the main form via a tab control.

Did try to place unbound text boxes onto the tab control and then link direct to the table but it would not work - this was the code I tried to use previously on a command button but could not get it to work:

Previous Code:
Dim db As Database
Dim rec As Recordset

Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from VehicleChecklistSubTB")

rec.AddNew
rec("CLStartDate") = Me.txtCLStartDate
rec("Driver") = Me.txtDriver
rec("MileageTD") = Me.txtMileageTD

rec.Update

Set rec = Nothing
Set db = Nothing


End Sub


Any pointers would be much appreciated. Pauline

 

[arnelgp]

its:

dim db as dao.database
set db=currentdb
db.Execute "INSERT INTO " _
& "[VehicleChecklistSubTB] ([CLStartDate], [Driver], [MileageTD]) IN 'z:\tmp.accdb' SELECT #" & Format(Me.txtCLStartDate, "mm/dd/yyyy") & "#, " & IIF(Me.txtDriver & "" = "", "Null", "'" & Me.txtDriver & "'") & ", " & IIF(Me.txtMileageTD & "" = "", "Null", Me.txtMileageTD) & ";"


just change the path and the db name (blue-colored)

 

[Pauline123]

HI arnelgp, thanks but as I an flitting between two computers would rather set the insert to current database than use a path - is there anyway that can be done ?

Plus would I only just use your coding or would I insert it into one of the codes I have added.

Many thanks

 

[Pauline123]

Hi thanks again for your help - found an easier way around the problem but I continue to learn thanks to you guys