How do I reference fields within tables using VBA in Access such that they can be edited?

Question

Specifically, I'm a n00b making a db. So far I have a form set up like I like it, but no values are bound b/c I need to switch between tables (I could probably just add all of the fields from the first table to the second table, but half of the point of me doing this myself was learning).

I've encountered two problems.

First, I'm having trouble making the form accept that I want its recordsource to be myTable. I have no idea why. It seems simple enough

form.RecordSource="myTable"

Unfortunately, it doesn't work. If I do that, text boxes that are bound to fields in myTable say #error or #name? instead of their intended value.

To try and fix this, I went into the properties of the form and set its control source to:

"SELECT * FROM myTable, myTable AS backup_1;"

I have no idea what that means, but that's what I had to do to get anything to work. This is my first problem - how do I 'properly' associate a db with a form without the shenanigans and have references work? I have done it 3 different ways now, one worked for a while then stopped, one (form.recordSource=myTable) simply didn't work, and the other is the first method I mentioned. All of them are causing me problems.

At any rate, I used the SELECT... since it's the closest to actually working. From there I set the text boxes that I didn't want to leave Unbound as [myTable.FieldName], and it worked. Programmatically I was able to use [myTable.FieldName] to edit get the information from a particular field from a particular record without difficulty. The problem is that I cannot seem to programmatically edit the data stored in those fields.
For example, if I try:

txtDisplayField1=[myTable.Field1]

Works fine. But if I try:

[myTable.Field1] = txtDisplayField1.text

I get run-time error 2448 - "You can't assign a value to this object." (Which is the most helpful error message ever.)

Earlier I mentioned that when associating my form with a table, there was one way that worked at first but then stopped working for some reason. Originally I just typed:

form.recordsource = myTable

and it worked just fine. I was able to reference fields by referring to them as Field1, etc., without the brackets. It worked as of me getting off work Friday (gasp - I'm not a programmer by trade) but didn't work when I got in. That's probably the most convoluted explanation of a problem humanly possible... but any assistance would be greatly appreciated.

Answer 1

I'm having trouble making the form accept that I want its recordsource to be myTable.

Changing the Recordsource of a form while the program is running is an advanced technique, and should be reserved for use only by the experts.

To try and fix this, I went into the properties of the form and set its control source to:

SELECT * FROM myTable

Correct, although your original SQL is a bit convoluted.

if I try:

txtDisplayField1=[myTable.Field1]

Works fine. But if I try:

[myTable.Field1] = txtDisplayField1.text

I get run-time error 2448 - "You can't assign a value to this object." (Which is the most helpful error message ever.)

Try this:

Me.Recordset.Edit
Me.Recordset("Field1") = txtDisplayField1
Me.Recordset.Update

Answer 2

You seem to be fighting against Access's default design.

If you have two tables with identical structure (you shouldn't in a well-designed database, but, hey, stuff happens), you can use the OnOpen event of the form to set the form's recordsource. A very simple (but not very user-friendly) way to do this is:

  Private Sub Form_Open(Cancel As Integer)
    If vbYes = MsgBox("Edit Table1?", vbQuestion+vbYesNo, "Choose table") Then
       Me.RecordSource = "Table1"
    Else
       Me.RecordSource = "Table2"
    End If
  End Sub

The reason that's a terrible UI is because you're asking a Yes/No question, but the answer is really "table1" or "table2." It's very easy to misunderstand the question.

Then all the controls on your form should be bound to the underlying fields. If you do that, you don't have to write any code to update the data -- users changing the data in the controls will change the underlying data automatically.