tabbed forms not setting variables 1

[Lhuffst]

I have a form that has 3 tabs. ON each tab, there are at least 2 subforms. ON the main form, I have a combo box for getting the inspectors name.
What I am trying to do is take the inspector name from the combo box and put it in the first field in each subform (to make sure that the correct inspector is getting credit for the work)
On the first tab, it shows up in one subform but I get an error message saying it can't find hte field on the second form. I have checked the form for spelling errors etc but all looks well.
CAn someone see what I'm doing wrong??
the code I'm using is

  txtmainwsscid = Me.cboLookUp.Column(3)
'make sure person logged in or selected in drop down box is passed
Me!frmReports.Form!Txtcboselected = Me!cboLookUp.Value
Me!frmSbDataEntry.Form!EmployeeName = Me!cboLookUp.Value
'Me!frmSbEquipRprTstBench.Form!EmployeeName = Me!cboLookUp.Value
'Me!frmSbFHMetersRepaired.Form!EmployeeName = Me!cboLookUp.Value
'Me!frmSbMeterTestingResults.Form!EmployeeName = Me!cboLookUp.Value
'Me!FrmSbLrgMetersIssued.Form!EmployeeName = Me!cboLookUp.Value
'Me!frmSbMeterCrates.Form!EmployeeName = Me!cboLookUp.Value
Me!FrmSbMetersInStock.Form!EmployeeName = Me!cboLookUp.Value
'Me!frmSbScrappedMeters.Form!EmployeeName = Me!cboLookUp.Value

The lines that are commented out are the ones that are not working.
Thanks for any help.
lhuffst

 

[MajP]

If you are confident that employeename is in the recordsource of those subforms, then if there is not employeename control add it. Even if you make it invisible. Without a long explanation there is a way to to lose the reference to a field. Once you add it to the form, if it works try to remove it.

 

[Lhuffst]

Thanks. I'll double check the recordsource. I checked the forms for the control name but didn't check recordsource.

 

[Lhuffst]

Sorry for the delay in responding - was temporarily on a different project.
I checked the recordsources and while the field was there, there wasn't an active record with that particular inspectors name.
If there is one, it comes up fine.
How do I handle the situation of when there isn't one? I need the forms to display the inspector name that is selected in the combo box so the new record can be added each day.

Thanks

 

[dhookom]

I'm not sure why you don't simply use the Link Master/Child properties of the subform controls. When you enter new records in the subforms, they will automatically default to the value from the "combo box for getting the inspectors name".

Duane
Hook'D on Access
MS Access MVP

 

[Lhuffst]

Hi, I do have them linked. If there is a child record, it works fine but if there isn't a child record yet, it throws error 2465.
What I'm trying now is putting the combobox value on the mainform into a global variable. When I load each subform I put in this code.

EmployeeName.SetFocus
EmployeeName.Value = g_myusername
DataEntrySessionId = Now()

That is at least bringing up the values now on each subform. It seems to work fine until I change the inspectors name and then it seems to work on some subforms and not others. I'm trying to add a requery statement now.

 

[dhookom]

Why do you think you need to use code to set the value of employeename? If the forms are linked this is redundant. You can also set the default value of DataEntrySessionID to Now().

Duane
Hook'D on Access
MS Access MVP

 

[Lhuffst]

On the main form, I have a combo box where you select the inspector name. I was trying to take that value and automatically populate the subforms with that name. I linked them by

link masterfields = cbolookup      
link childfields = empabbrv      (actual field name)

on cbolookup, the value that I need to capture is actually column 0.

This worked ok for the tables that had a record for the inspector but it left it blank and threw an error if there wasn't a record. I didn't want to bog down the tables with blank data so I tried to put it in a variable that I could use for each of the subforms.
There are 11 different types of inspections that they do but not all on the same day but they are required to enter into at least one subform on a daily basis.
I tried to pass the value to the subform using this pattern

Me!frmSbDataEntry.Form!EmployeeName = Me!cboLookUp.Value
'Me!frmSbEquipRprTstBench.Form!EmployeeName = Me!cboLookUp.Value

That worked as long as they had a record in the subform table. The one commented out threw the error because there wasn't a record.

I also put the cbolookup value in a text box and used the text box to link on the tables. This seems to work better because I'm not getting the error message now but i still have the problem of how to handle the subform if there isn't a record in the table that matches the cbolookup value. That was why I tried to force the values by the code

EmployeeName.SetFocus
EmployeeName.Value = g_myusername

How would I do this and not be redundant? Thanks for the help

 

[Lhuffst]

I just added the following to the onload for each subform

'Debug.Print Forms!frmtab.[txtUserName]
If IsNull(Me.EmpAbbrv) Then
       Me.EmpAbbrv = Forms!frmtab.[txtUserName]
    End If

This seems to work. The first time in, it didn't set on 1 subform but when I tried again, it seems to work. Would you recommend this method or a different method?

 

[dhookom]

I still don't understand why you need any code. The link master/child will show only the related records. Any new record will be related. If there is no record matching in the subform, a blank will display. What's wrong with that?

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

To echo what Duane is saying, many people do not realize that you can link a subform to an unbound control on the main form. The only issue is that you have to type the control name into the link master fields property. The wizard does not give the option of picking a control name.

If I have an unbound control that holds inspector names and link it to a subform/s. Then when you change the unbound control only records for that inspector appear and any new records entered in the subform will automatically recieve the foreign key for that inspector. No code required.

The errors you are getting is because you have additional unneeded coded as Duane is stating.

 

[Lhuffst]

The reason I wanted the name and date to come across is because there will be multiple people using the same forms/tables. When they updated or added new info, I needed the names and dates to be saved with the record.

 

[dhookom]

Terms like "come across" don't carry much specific meaning. If you want any new records created in the subforms to have the inspectors name and the current date, simply use the link master/child as well as setting a default value of Now() on the field in the table or in the control on the subform.

Have you even tried this? If you haven't, please give it a shot. If you have tried this (no code) and it didn't work, you need to state what didn't work as expected.

BTW: do you have tables related on an inspector name rather than some type of primary key ID value?

Duane
Hook'D on Access
MS Access MVP

 

[Lhuffst]

Using the linking as you suggest does work but it wasn't saving the inspector name in the subform tables. My thought was that since the original design had duplicate table(s) for each inspector, I needed to compare the new relational way to the old way. Once that was vetted, then I would be using the primary key ID value as you state.

 

[dhookom]

You should only be storing the foreign key values, not the name fields.

Maybe you need to provide the significant tables and fields particularly the primary and foreign keys. The issue might be related to a table design.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

My thought was that since the original design had duplicate table(s) for each inspector, I needed to compare the new relational way to the old way. Once that was vetted, then I would be using the primary key ID value as you state.

Subforms are very powerful. Whatever the Master fields are will filter on the linked child fields with the same values. All newly created subform records will default to these master fields values. This occurs even if no relationships are established in the database.

To demonstrate, assume my subform has two numeric fields, A and B. On the main form I have two unbound controls txtA and txtB. I can link master fields: [txtA];[TxtB] to child fields [A];. If I type values in txtA and TxtB it will filter ther subform on those matching values. Also any new records created in the subform will have txtA's value in field A and txtB's value in field B.

 

[dhookom]

My concern is there is a value/field on Lhuffst's main form that is dependent on the another field in the main form and wanting that value in the subform. For instance having a EmpAbbrev (primary key for employee table) and EmployeeLastName in the main form and wanting the EmployeeLastName (in addition to EmpAbbrev) in the subform. Storing the EmployeeLastName in more than one table would not be the proper table structure and attempting to implement this would cause issues.

If you want the employeeLastName to display in the subform, you can do this with a combo box.

Duane
Hook'D on Access
MS Access MVP

 

[Lhuffst]

Here are the tables and fields I am using
TblUsers

Userid - PK​

WorkgroupID -FK to security tbl​

UserAbbrv​

UserName​

WSSCID​

UserType​

tblDataEntry

ID - PK​

UserID - FK (link to tblusers)​

SessionID​

Dateadded​

DataEntrytype​

Sheets​

tblMetersScrapped

MetersScrappedID - PK​

UserID - FK (link to tblusers)​

SessionID​

Dateadded​

BronzePlastic​

NoMetersScrapped​

tblMetersScrapped

YesNosID - PK​

UserID - FK (link to tblusers)​

SessionID​

Dateadded​

Warehousse​

NoTruckInspection​

I had originally tried to pass empabbrv and empname because the original design did not have any keys. If I had gotten that to work, then I was going to add a id field to being the key and use that. However, based on your comments I did make a key in tblusers and then add a field to each table as a FK -- Thank you for the help. This works but I still have my original issue.

When I enter data, the id is not being stored in the subform tables userid field(tbldataentry, tblmetersscrapped, tblyesnos). This is why I was trying to capture the id in a global variable and put it in a textbox on the individual subforms. After reading your last post, I tried to check and see if I missed anything but if I did, I dont' know what I missed. On the relationships diagram, it has
blUsers.Userid (autonum) as 1 to many to Userid (number) for each of the tables.

I also made a relationship diagram (first time ). Doing this, should it automatically store the user id in the subform tables?

 

[Lhuffst]

Also, does it matter if you make the relationships on the backend or frontend? I did it on the backend.

 

[dhookom]

Did you set the link master/child properties to the UserID fields?

I assume two tblMetersScrapped tables is a pasting error.

Duane
Hook'D on Access
MS Access MVP