Tab Controls with Subforms Issue

[harvesterlily]

I am working on designing a db that will manage our client maintenance and processing. It's slightly broader than that. There has been a much needed push to centralize info in our company. Except for being available for viewing on our website, it's not being utilized.

Needed info is located in several places:
Our IT dept has db that contains db type, dsn, connection info and various passwords for each client. They are usually first to be aware of this info as they do client installs. So as not to alarm anyone, this info will run hidden in processes in a backend db.

We have a website that contains various addresses, contact info, etc... I have access to these tables as well.

I am looking to bring in each of these pieces into a client setup/maintenance form. I would like to do this sort of like a wizard.

I've created the various relationships to all of these tables. My goal is to utilize any data that exists or create it where needed if it doesn't.

Setup/Maintenance
Main form has two searchable combo boxes. One allows you to search for client by client code or by client name. If you search by client code, I have a procedure that hides the client name combo box and fills a client name text box that is made visible. Vice versa if searching by name. This is working. To be added, a process that allows user the option to add client code/name if not already in list.

From there I have added tabs that contain subforms. I'll explain what I am trying to accomplish before going into problems I've run into.

This form will have several tabs:
I would like to have a form for each address that appears from our web detail tables. Possible for there to be up to four existing for each client. Basically one record per tab/subform. Want tab caption to change as addrname field indicates. I'm guessing that I'd have these tabs existing but hidden unless contains data.

From there,question on main form appears or pop-up that asks if this is a group client. If yes, query will search to see if this particular group receives client statements.
If so, a group address tab should appear with detail. No just proceeds on to next step.

This is the basic idea. I'm guessing that there could possibly be as many as 12 tabs. There will be a pricing survey that determines/displays tabs for relevant services/products, etc... I figure when I can get the first couple up and running, it will be the same syntax with the remainder.

So far....
The client that is selected in the combo box is then passed to the subform recordsource via a sql stmt. I am able to get only one client to match and display tab/subform. It's the first in list. I've tried other clients that do have records. Some show me my subform with #Name# in form fields, then tab/form disappears. Others just show me a blank page. I haven't gotten too far along with this, so I am not opposed to starting afresh with a new db if necessary. My system is tied up running daily processes, but will be glad to post my code if needed when these are done.

thanks in advance

 

[Trevil]

Hello,
Skipping to your last paragraph where you're encountering problems, I suspect there is possibly a data / SQL / Refresh issue. A couple of weeks ago I had the same '#Name' problem. Turned out that the SQL string was doing a 'GroupBy' on a Boolean field that was in an optional table (Left Join). Changed SQL to treat Boolean field differently and problem went away (had 'iif' statement reformat to text field).

Re not getting records for other clients, I assume you may have an 'AfterUpdate' event that sets the SQL and requeries the form / control, or do you have the SQL reference the combo box value?

Is there code in the "Form Load" event that sets anything? If the resulting recordset for a form returns zero records, then the "Form Load" event will never fire (thus any code there can be useless).

Let me know if any of this helps.
Wayne

 

[harvesterlily]

Thanks Trevil. It was a combination of the sql statement that I had built not being assigned to the recordsource of the subform(duh!) and refreshing the form. The subform would display but with the same client, as they are the first client in the results of the underlying recordsource. Using the same underlying query I am passing the where clause from my combo boxes. Once I figured that out I was hampered another day until it hit me that I wasn't refreshing the form.

Now that I can get my results to change based on client, I am trying to make each address that returns for a client to appear on a different tab. This is the code that I've gotten to work so far:

After builing the sSQL1 statement:
Set rs1 = db1.OpenRecordset(sSQL1)
If rs1.RecordCount > 0 Then
'MsgBox sSQL1
Me.TabCtl71.Visible = True
Me.Page72.Visible = True
Forms!frm_Test3!Street1.SourceObject = "Addr1"
Forms!frm_Test3!Street1.LinkChildFields = cadfk_cltpk
Forms!frm_Test3!Street1.LinkMasterFields = cltpk
Forms!frm_Test3!Street1.Form.RecordSource = sSQL1
Forms!frm_Test3!Page72.Caption = Forms!frm_Test3!Street1.Form.cadAddrName
Forms!frm_Test3!Street1.Form.Refresh
Forms!frm_Test3!Street1.Form.Visible = True
Else
'displays label that states that no addresses exist for this
'client
'will replace with function to allow user to add addresses
Me.Label84.Visible = True

End If

Since I am assigning a source object to a subform space("Street1") which resides on my tab(Page72), I was thinking that as I loop thru a recordset into a select statement that I could increment the necessary objects and reassign these as follows:

i = rs1.recordcount
x = 1
Do until x > i

Select Case x
Case 1
Me.Page72.Visible = True
Forms!frm_Test3!Street1.SourceObject = "Addr1"
Forms!frm_Test3!Street1.LinkChildFields = cadfk_cltpk
Forms!frm_Test3!Street1.LinkMasterFields = cltpk
Forms!frm_Test3!Street1.Form.RecordSource = sSQL1
Forms!frm_Test3!Page72.Caption = Forms!frm_Test3!Street1.Form.cadAddrName
Forms!frm_Test3!Street1.Form.Refresh
Forms!frm_Test3!Street1.Form.Visible = True
Case 2
Me.Page73.Visible = True
Forms!frm_Test3!Street1.SourceObject = "Addr2"
Forms!frm_Test3!Street1.LinkChildFields = cadfk_cltpk
Forms!frm_Test3!Street1.LinkMasterFields = cltpk
Forms!frm_Test3!Street1.Form.RecordSource = sSQL1
Forms!frm_Test3!Page73.Caption = Forms!frm_Test3!Street1.Form.cadAddrName
Forms!frm_Test3!Street1.Form.Refresh
Forms!frm_Test3!Street1.Form.Visible = True
Case Else
End Select

x = x + 1
Loop


Questions:
Do I need a separate subform space(Street2) for each tab? Is that possible?

Sorry that the code is so crude. I pare down once I get everything functional. Though not shown here the sql statement will be changing to display only one address per form. The tab caption is changing based on address name that is in the table.

Hope that makes sense.

 

[Trevil]

Being the lazy slob that I am.... the easiest / less intensive approach may be to do the following:
1. You already have a form that allows you to select the client, and there must be a column in the combo box with the client's key. If not, you could add a hidden text box on the form with the key value.
2. Create an address subform.
3. Insert that subform on each of the four tabs.
4. With your existing code where you loop through all of the records, you already set the "Caption". You may want to hide tabs if < 4 records.
5. Add code so that when you click on any tab that it does one of the following:
a. Generates SQL for tab that selects that client and that address type.
b. saves the "Caption" in a hidden field and requeries the subform for that tab. This means your address subform SQL will specify "Where Client=" and where "AddressType=" that points to the hidden control.

Does this help?
And don't worry about "Sorry that the code is so crude" -- I find that my "quick and dirty code" usually remains dirty!

Wayne



35+ years of 'progress' -- can't we all just go wire boards again?

 

[harvesterlily]

Trevil,

Just now getting back to this project. I think we are on the same wave length. I only want to present tabs for the particular address types that exist. The address name in the table is not limited to certain types, so I'm changing the caption based on the field value. One thing that I'm running into is that because I am working with several dbs that the primary key is not necessarily the same between dbs. In one table the client code is the pkey. In another it is the fkey, but is numeric. I'm trying to bring all this data into one form. Give me a day or so and I will work with your options and see what I come up with.

Thanks
Teresa