Hello, I’m hoping that someone can help me with an issue. I’m a SQL developer, and new to Access and VBA, but I have been given a task of taking a current Access application and changing the back end to run from SQL Server. I’ve been stumbling along, but have run into an issue that I can't seem to fix. I need to link my Main form to a Sub form. The forms use to be linked by a product ID, but now the forms are unbound and as I understand you can’t link forms when they are unbound. Is there a way to link the forms through VBA code and if so, how can I do that?
What I have been attempting to do was try and get the Main forms Product ID and use that in my SQL where clause in the sub form. But have not been able to get it to work. At times, it seems to work, but then I click to the next record or reopen the forms, and I get errors. Other times Access hangs and then just closes.
Here is my code currently for my SQL statement:
.Open "select Table1.ProdID, Table2.Field, etc… " & _
" from Table1 " & _
“ Inner join Table2" & _
" on Table2.ProdID = Table1.ProdID; "
' " where Table1.ProdID = " & Forms![MainForm]!MainProdtxt.Value & " ;"
When I try and debug the Forms! Statement I get the following errors:
“The expression you entered has a field, control or property name that Access can’t find”
Sometimes, I’ll get: “This property isn’t available in Design view.
The thing is, it seems to work, but only on the first record and once you move to next record it breaks.
I hope this was enough information, please let me know if I should provide anything else.
Any help will be greatly appreciated!!
Thanks!
Ty
What I have been attempting to do was try and get the Main forms Product ID and use that in my SQL where clause in the sub form. But have not been able to get it to work. At times, it seems to work, but then I click to the next record or reopen the forms, and I get errors. Other times Access hangs and then just closes.
Here is my code currently for my SQL statement:
.Open "select Table1.ProdID, Table2.Field, etc… " & _
" from Table1 " & _
“ Inner join Table2" & _
" on Table2.ProdID = Table1.ProdID; "
' " where Table1.ProdID = " & Forms![MainForm]!MainProdtxt.Value & " ;"
When I try and debug the Forms! Statement I get the following errors:
“The expression you entered has a field, control or property name that Access can’t find”
Sometimes, I’ll get: “This property isn’t available in Design view.
The thing is, it seems to work, but only on the first record and once you move to next record it breaks.
I hope this was enough information, please let me know if I should provide anything else.
Any help will be greatly appreciated!!
Thanks!
Ty