How to use lookup table values in a form as a Label

[MP BILL]

Hello, I am a novice when it comes to Access programming. I am trying to take a values set in a table (by Pt#) and use them on a new input form as a label only. Would I create the form as a sub form and Lock the fields? Help is greatly appreciated

[June7]

A subform is not necessarily needed to show related info from lookup table. Possibly you just need to join the tables in form's RecordSource and set the jointype to 'Include all records from <master table name>...', then bind and lock textboxes. Would have to know more about data structure. Want to provide file for analysis? Follow instructions at bottom of my post.

[MP BILL]

A subform is not necessarily needed to show related info from lookup table. Possibly you just need to join the tables in form's RecordSource and set the jointype to 'Include all records from <master table name>...', then bind and lock textboxes. Would have to know more about data structure. Want to provide file for analysis? Follow instructions at bottom of my post.

I have attached the File as you requested. I am looking to:

a) take values set in a table (Tbl Value Set)Based on the part number selected at top of inspection main form and use the value as a label for an input form (FrmIn_InspDataSubform)
b) also use the values to set conditions on the input fields of (FrmIn-InspDataSubform).

Your help is greatly appreciated...

[June7]

So you are not trying to print a set of labels (mailing labels, folder labels). You want to set the Caption property of the label controls on FrmIn_InspDataSubform also to set the ValidationRule property of textboxes? Never done anything like this. Expect will need VBA code in the form Current event. This code behind FrmIn_InspDataSubform works:

Private Sub Form_Load()
Dim i As Integer
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM TblInspSet_Values WHERE cboSPartNumID=" & Form_FrmIncoming_Insp.cboSPartNumID & ";", CurrentProject.Connection, adOpenStatic, adLockPessimistic
For i = 1 To 8
   Me.Controls("strINSPdata" & i & "_Label").Caption = rs.Fields("strINSPV" & i)
   'following is suggestion for the ValidationRule property, not tested
   Me.Controls("strINSPdata" & i).ValidationRule = rs.Fields("field name prefix here" & i)
Next
End Sub

Need to set VBA reference: Microsoft Office 12.0 Object Library

[MP BILL]

June7...added code to as Event procedure on the Subform "On Load"... Code does initially change the labels, however, when I add the next record, the labels remain the same...(do not change with the selected pt#). Should I add code to re-query and move code to "On Current" to handle record changes. What you have provided has been much help and much aprreciated...

[MP BILL]

Code works well under "On Current" I still need to look at why I am getting a type mismatch dialog when changing record.

[June7]

Okay, the code will have to be executed when the forms open and when a part number is selected. This means code behind the main form that refers to the subform. I am having problem with syntax for that.

I notice there are no values for labels 9 and 10 so the code errors if changed to loop 10 times. Also, the code will error if no matching record in TblInspSet_Values.

Why using combobox DblClick event instead of AfterUpdate?

[MP BILL]

I am sorry, I do not understand exactly where the code needs to go? On what form or what condition (e.g. Main form (FrmIncomin_Insp) under "On Load" as an event procedure.....) I apologize as being a novice has it draw backs...

[MP BILL]

the double click is being used to allow the user to add a part number that may not be there
Parts being inspected could have 1 - 10 inspection criteria's, the reason for empty (null) values in certain fields...

[MP BILL]

Parts being inspected could have 1 - 10 inspection criteria's, the reason for empty (null) values in certain fields...

[June7]

I have the procedure under the Load event of the subform and am trying to have code in the AfterUpdate event of combobox call the subform Load event. I know how to do that with primary forms but syntax for calling procedure of subform (in this case a subsubform) eludes me.

Right now there is no error handling in the code.

[MP BILL]

all Forms are linked via the the master and Child Fields... Would that need to be called out as the identifier to the part number and subform fields

[June7]

The Master/Child fields are not pertinent to the issue. The issue is syntax for referencing form (and its controls) when it is the SourceObject of a subform container control. The subform container control is a 'middle man' and must be considered in the syntax. This is what I am struggling with.