How can I link fields in tables using VBA in Access so that they can be edited?
I am new to creating db. So far, I have the form configured the way I like, but no values are related. B / c I need to switch between tables (maybe I could just add all the fields from the first table to the second table, but half of them the point I am doing myself was learning).
I faced two problems.
First, I am having trouble accepting the form that I want its record source to be myTable. I have no idea why. It seems simple enough
form.RecordSource="myTable"
Unfortunately it doesn't work. If I do this the textboxes bound to the fields in myTable say #error or #name? instead of their intended meaning.
To fix this, I went into the properties of the form and set its control source:
"SELECT * FROM myTable, myTable AS backup_1;"
I have no idea what this means, but this is what I had to do to get anything to work. This is my first problem - how do I "properly" link the db to the form without shenanigans and have the links to work? I did this in 3 different ways, one worked for a while and then stopped, one (form.recordSource=myTable)
just didn't work, and the second was the first method I mentioned. They all cause me problems.
Anyway, I used SELECT...
since it works the closest. From there, I set the textfields I didn't want to leave Unbound as [myTable.FieldName] and it worked. Programmatically, I was able to use [myTable.FieldName] for editing to get information from a specific field from a specific record without difficulty. The problem is that I cannot programmatically edit the data stored in these fields.
For example, if I try:
txtDisplayField1=[myTable.Field1]
Works great. But if I try:
[myTable.Field1] = txtDisplayField1.text
I am getting runtime error 2448 - "You cannot assign a value to this object". (This is the most helpful error message.)
Earlier I mentioned that when linking my form to a table, there was one way that worked at first, but then somehow stopped working. Initially, I just typed:
form.recordsource = myTable
and everything went great. I could refer to fields by referring to them as field1, etc., without brackets. It worked when I left work on Friday (sigh - I'm not a programmer by trade), but it didn't work when I got in. This is probably the most confusing explanation of the problem humanly possible ... but any help would be greatly appreciated.
You seem to be struggling with Access's default design.
If you have two tables with the same structure (you don't have to in a well-designed database, but hey, something's happening), you can use the form's OnOpen event to set the source of the form's records. A very simple (but not very convenient) way to do it:
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 the interface is terrible is because you are asking a Yes / No question, but the answer is really "table1" or "table2". This is very easy to misunderstand.
Then all the controls on your form should be bound to the main fields. If you do this, you don't need to write any code to update the data — users who change the data in the controls will automatically change the underlying data.
source to share
I'm having trouble creating a form, agree that I want it to be myTable.
Changing the record source of a form while the program is running is an advanced technique and should be reserved for expert use only.
To fix this, I went into the properties of the form and set its control source:
SELECT * FROM myTable
Correct, although the original SQL is a bit confusing.
if i try:
txtDisplayField1 = [myTable.Field1]
Works great. But if I try:
[myTable.Field1] = txtDisplayField1.text
I am getting runtime error 2448 - "You cannot assign a value to this object". (is the most helpful error message ever.)
Try the following:
Me.Recordset.Edit
Me.Recordset("Field1") = txtDisplayField1
Me.Recordset.Update
source to share