Writing to a table

[jamsta]

Hello,

I'm writing some VBA in Access, part of which is to write to a table. I'm using this:

Set db = CurrentDb
Set rst = db.OpenRecordset("tblParcels")

rst.AddNew
rst!ID = 1
rst!URN = "TEST"
rst.Update

this = rst!URN
MsgBox this

However, when I run the code and then look at the table no update has taken place. I'm not getting any error messages. If I update the table manually and then run the last two lines above to display the value in rst!URN, I do get the right output (I've Dimmed everything earlier in the code).

I think I'm missing out a fundamental step somewhere. For now I've resorted to updating the table using SQL, but this might not be the most efficient way of doing it. Any general advice on the best way to write to a table in VBA would be appreciated.

thanks
jamsta

 

[ajetrumpet]

not sure...

what is rst dimmed as?
try opening the recordset fully before adding a new record. e.g.:

rst.movelast
rst.movefirst

is there other code running somewhere that interferes?
maybe try opening a dynaset instead of just the table?

db.openrecset("table", dbopendynaset)

are you destroying the objects at the end by setting them to NOTHING?
do you have the table physicaly open the whole time you are running the code? if so, updates don't appear until you close it re open it in the db window


just spitballing for you

 

[gemma-the-husky]

this should work

it might be that the update is failing for some other reason. do you have error handling that might deal with that.

try using a breakpoint to step through the code, and see exactly whats going on.

 

[jamsta]

Thanks - you may only have been spitballing but I've done some more playing around based on what you've said and I think I've cracked it.

rst is a recordset but I'd used it earlier to represent a different table, and I hadn't been closing and setting to nothing - AND I was running the code with the table open! All in all I wasn't exactly following best practice it seems.

thanks again,

jamsta.

 

[lcook1974]

I am having a similar problem but only one of my fields isn't updating to the table.

It is the date field which an unbound text box.

Could you take a look and give me some direction on where to go? The PreceptorDateCompleted is the culprit. I have everything spelled the same as the unbound text box and table field.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
Dim f As Form

Set f = Forms!frmCompletedTraining
Set db = CurrentDb()
sql = "Select * From CompletedTraining"
Set rs = db.OpenRecordset(sql)
If ((Me.cmbEmployee <> "") And (Me.cmbPreceptor <> "") And (Me.PreceptorDateCompleted <> "") And (Me.List10 <> "")) Then
  rs.AddNew
     rs!EmployeeID = f!cmbEmployee
     rs!PreceptorID = f!cmbPreceptor
     rs!PreceptorDateCompleted = f!PreceptorDateCompleted
     rs!ObjectiveID = f!List10
     
End If
MsgBox "You have successfully updated the completed training table", vbOKOnly

Larry

 

[boblarson]

I'm afraid that using the bang has been the problem (pretty sure). Because you are using

f!

instead of

f.

you are trying to use the FIELDs instead of the CONTROLS. Since the other controls are bound to their fields and you likely have the same control names as the field names using f!FieldName is working for the bound fields. The unbound however does not have a field and therefore f!fieldname doesn't work. You need to use f.fieldname

So, change this:

rs!PreceptorDateCompleted = f!PreceptorDateCompleted

To this

rs!PreceptorDateCompleted = f.PreceptorDateCompleted

and it should work.

 

[lcook1974]

Thanks Bob! but that didn't work either.

I tried:

f.PreceptorDateCompleted
Forms!frmCompletedTraining.PreceptorDateCompleted

any other suggestions? I am thinking of just re-writing it using an INSERT INTO query if I can't get this to work.

Larry

 

[lcook1974]

yeah....so when I "unbound" the other text box, nothing updated or was added.

I'll go back to square "0" and try another method...after all there is more than one way to skin a cat. right?

Thanks!
Larry

 

[gemma-the-husky]

what format is the date on the form control

is it a short date, or is it text. if its not a date format, you will need to convert it before you can assign it, surely.

put a breakpoint there, and see what you actually have

 

[lcook1974]

This is did it for me....

INSERT INTO CompletedTraining ( ObjectiveID, EmployeeID, PreceptorID, PreceptorDateCompleted )
SELECT Forms!frmCompletedTraining!lstObjective AS objective, Forms!frmCompletedTraining!cmbEmployee AS Emp, Forms!frmCompletedTraining!cmbPreceptor AS Trainer, forms!frmCompletedTraininig!PreceptorDateCompleted AS Datecomplete;

Thanks again!