Using VBA to Link Main Form to Sub Form? (1 Viewer)

TyFer

New member
Local time
Today, 03:11
Joined
Dec 6, 2015
Messages
9
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
 

JHB

Have been here a while
Local time
Today, 11:11
Joined
Jun 17, 2012
Messages
7,732
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?
Why are the forms unbound, why do they not have a recordsource, it shouldn't change only because the backend is running on a SQL-Server?
 

TyFer

New member
Local time
Today, 03:11
Joined
Dec 6, 2015
Messages
9
I had initially set the main form to use a pass through query, but part of the form has to update to a table so a pass through did not work. Is there a way to assign a SQL query to a Record Source through VBA and be able to keep it updateable? I'm afraid I am at a lost.

The Sub form is read only , so could I make that form bound? Would that help to keep the product ID with a master/child relationship?

Thanks so much for the reply.
 

sneuberg

AWF VIP
Local time
Today, 03:11
Joined
Oct 17, 2014
Messages
3,506
I think you might be able to control what's displayed in the subform by using its filter without have to have these links. So whenever the record changed in the main form you would change the filter of the subform. If you google "ms access filtering subform" you probably can find something that will get you started.
 

TyFer

New member
Local time
Today, 03:11
Joined
Dec 6, 2015
Messages
9
Thanks for the reply.. I will do some research on filtering.
 

JHB

Have been here a while
Local time
Today, 11:11
Joined
Jun 17, 2012
Messages
7,732
Why is the record source of the main form and the sub form is not set to the respective tables? Too much data, too slow or ??
Regarding the updating of data you're writing about, I can not really find out whether data is updateable or not or you don't want them to be updated.
If the data isn't updateable it could be because the table hasn't any primary key.
 

TyFer

New member
Local time
Today, 03:11
Joined
Dec 6, 2015
Messages
9
JHB, I'm not sure I know how to set the forms to the respective tables. I've been using ADODB to connect to SQL and do my selects. My tables are on SQL Server, would just linking my table to Access for the Main form be a better option?

In regards to the updating, most of the main form has read only fields, but there are a few fields where changes can be made to the data. My table does have a primary key, and I've actually got the updating part working. It's because of this need to keep track of updates that I thought the only option was to use ADODB through VBA.

Thanks,

Ty
 

WayneRyan

AWF VIP
Local time
Today, 10:11
Joined
Nov 19, 2002
Messages
7,122
TyFer,

You should use linked tables for your parent/child tables.

Google for the Parent-Child links mentioned above. They are not
filters but properties of the form.

You can Set "Enabled=False" for the fields you don't want updated.

You can set up an Audit Trail either in Access or with triggers in SQL Server.

hth,
Wayne
 

JHB

Have been here a while
Local time
Today, 11:11
Joined
Jun 17, 2012
Messages
7,732
Linking the subform using VBA:
Code:
Me.SubFormControlName.LinkMasterFields = NameOfField
Me.SubFormControlName.LinkChildFields = NameOfField
 

TyFer

New member
Local time
Today, 03:11
Joined
Dec 6, 2015
Messages
9
Thanks to everyone for the comments. I ended up taking WayneRyan's suggestion and Linked my tables. I think I was over complicating things. Once my tables were linked, I was able to link the field I needed on the Main form to the Sub form and now things are working!

Thanks so much for the quick replies. I'm sure I'll have more questions as I work through this project!

Ty
 

Users who are viewing this thread

Top Bottom