Populate Label caption from records

[Priyan]

Hello everyone

*

I got this situation and it would be nice if someone can advice me how to proceed.

*

In the tblMachines I’ve got 4 fields

Section********** MachineNumber***** MachineName********* MachineCapacity

G5****************** 1254A* * * * * * * * * * * * * * Maki************************** 500

G5****************** 3544************************* Zebra************************ 1000*

G5****************** 1565A* * * * * * * * * * * * * * Zebra 2********************* 5040

Fitting*********** 1565A* * * * * * * * * * * * * * *fit 1*************************** 100

Fitting*********** 1545B* * * * * * * * * * * * * * *fit 2*************************** 500

*

In the FormSectionLayout I have a dropdown list to select the section.

On the same form I have three (max number of machine for a given section) labels.

*

What I want to do is when I select the section from the dropdown list, the value of the machineNumber, MachineName and MachineCapacity* to populate in to label.caption

*

Example1 section G5 is selected.

*

First Label caption:

1254A

Maki

500

*

Second Label caption:

3544

Zebra

10000

*

Third Label caption

1565A

Zebra 2

5040

*

Example 2 section “Fitting” is selected

*

First Label caption

1565A

fit 1

100

*

Second Label caption

1545B

fit 2

500

*

Third label caption

Empty

*

What is the best way to achive this with DLookUp or with SQL?

We have about 5 Sections and maximum 6 machines per section.

*

Your help is really appreciated.

*

Priyan

 

[The_Doc_Man]

You cannot bind a label to an underlying record. Therefore, the only way I can see to do this without breaking something is to have some code in your form's OnCurrent event to directly populate the labels via

[label].caption = {something specific}

Do that once for each label you want that way, but don't forget that once you choose this path, EVERY label that you COULD reset MUST be reset in the OnCurrent event. (Because otherwise you might be looking at leftovers.)

 

[Banana]

I'd also point out that you can have your textbox formatted to look like a label so there's really no reason to use a label to display data.

 

[Priyan]

Hi Guys

Thank you for the quick responce.
Ok i'll change the Labels to text boxes.

And i also got this SQL filter the records by section.

Could someone tell me how to writhe the records in the recordset one by one to the text boxes.

regards

Priyanga

Sub FillWorkCenter()

Dim strSQL As String
Dim db As Database
Dim rec As Recordset

strSQL = "SELECT tblAnlagen.Bereich, tblAnlagen.ProdPlanPos, tblAnlagen.Arbeitsplatzt, tblAnlagen.Anlage_Bezei, tblAnlagen.CapasitätProSicht, tblAnlagen.Schicht " & _
"FROM tblAnlagen " & _
"WHERE tblAnlagen.Bereich= ' [Forms]![frmProdPlanEingMain]![komSelectBereich] "


Set db = CurrentDb
Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)

 

[Priyan]

Hello everyone
I was able to find the solution on my own.
I’ll post the VBA coding here. It might come in handy.


Functions of the code:
1. It picks the value of Combo box in a form
2. With the SQL statement the records in tblAnlagen will be filtered by the value found in previous combo box. (In the example the maximum number of filtered records are 6)
3. In the Form I have 6 predefined Labels called “lblAnlagenL1” to “lblAnlagenL6”
4. The latter part of the code it writes the results of the query in to the Label captions.
5. One record per Label
Regards
Priyan



Sub FillWorkCenter()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String

Dim LNummer As Long
Dim Labelname As String
Dim Bereich As String

Dim Anlage As String
Dim Anlage_Bezei As String
Dim CapasitätProSicht As String



LNummer = 1

‘Save the value of the combobox to the string
Bereich = Forms("FrmProdPlanLayout").Controls.Item("komSelectBereich")


Set db = CurrentDb()

‘SQL filters records from the tblAnlagen. “Bereich” is a string.
strSQL = "SELECT tblAnlagen.Bereich, tblAnlagen.Anlage_Nr, tblAnlagen.Anlage_Bezei, tblAnlagen.CapasitaetProSicht " & vbCrLf & _
"FROM tblAnlagen " & vbCrLf & _
"WHERE (((tblAnlagen.Bereich)= '" & Bereich & "')) " & vbCrLf & _
"ORDER BY tblAnlagen.ProdPlanPos;"

‘Till EOF of the SQL query
Set rs = db.OpenRecordset(strSQL)
With rs
While Not .EOF

Anlage = ![Anlage_Nr]
Anlage_Bezei = ![Anlage_Bezei]
CapasitaetProSicht = ![CapasitaetProSicht]

.MoveNext


‘the fields of the each Record are passing to Label caption property
Forms("FrmProdPlanLayout").Controls.Item("lblAnlagenL" & LNummer).Caption = Anlage _
& vbCrLf & vbCrLf & _
Anlage_Bezei _
& vbCrLf & vbCrLf & _
CapasitaetProSicht _
& vbCrLf & _
"Btl."

LNummer = LNummer + 1


Wend


End With


End Sub