Writing VBA results to table

[Vidar13]

This is probably a really stupid question, but I want to commit values to a table for a record that is already partially populated, without using a form and VBA code only.

I have no problems coming up with the values I want, but without using a form to bring focus to the record I want to change, how do it with VBA code? The one simple fact about the record I want to update is that is it always the last record in the table.

 

[Darrylles]

Hi,

Does this help...
[tt]
Set rsMyTable = CurrentDb.OpenRecordset("tblMyTable", dbOpenDynaset)
rsMyTable.MoveLast
rsMytable.myFieldA = "A"
rsMytable.myFieldB = "B"
rsMytable.myFieldC = "C"
rsMytable.Close
[/tt]

Regards,

Darrylle
"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[wemeier]

Darrylles is almost right. However, when assigning values to a field in a recordset, you must use the ! instead of .:

rsMytable!myFieldA = "A"

 

[Darrylles]

Thanks wemeier,

Always get bangs 'n dots mixed up (damned confusing - lol).
Always assume bang means control reference and dot means property or method reference.

Have you got a rule of thumb for this (please!!!!!!)?

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[wemeier]

I generally use the dot for everything EXCEPT when referring to fields within a recordset. Access seems to allow dots and bangs interchangeably except in this situation.

I like using the dot in VBA coding because Access displays tooltips based on the context of what I'm typing. As soon as I use a bang the tooltips stop.

Hope this helps.

 

[GComyn]

I usually use the following:

rsMytable.Fields("myFieldB&quot.Value = "B"


it's more explicit, and let's me see exactly what is going on.

GComyn

 

[Vidar13]

Okay, so say I wanted to APPEND a new record? Would I use a similar approach, only changing the recordset method?

 

[GComyn]

After looking at the above code that Darrylles first posted, I have a change that you should probably use:

Set rsMyTable = CurrentDb.OpenRecordset("tblMyTable", dbOpenDynaset)
with rsMyTable
   .AddNew
   .fields("myFieldA").value = "A"
   .fields("myFieldB").value = "B"
   .fields("myFieldC").value = "C"
   .Update
end with

That is how I add records to a table.

If you want to "update" information in a recordset, you would use the .edit method instead of the .addnew method.

You should always use the .update method after adding/changing the recordset, as that will insure that the data you added/changed was updated.

GComyn

 

[Vidar13]

Thank you very much everyone! I think I have a grasp on this now.