Create records from forms Label Captions & Textbox Names

[jw5107]

Below is code I'm working with... Not sure if I'm doing this right... I need to be able create records based on the forms label captions and textbox names... If there are labels that are blue in font color, then that labels caption will be saved as a record, and if a text box equals any labels captions, then the textbox value will be saved in the same record with the label caption.... Sure hope this makes sense...!!!
There could be several labels that are blue in fontcolor - so I need to be able to save as a recordset - I suspect anyways.....
Any suggestions on how I can get this to work...??
Thanks in advance..!!
jw5107

Dim ctrl As Control
Dim db As Database, rsAllocHist As Recordset
Dim strAlloc, strGTWY As String

For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
Select Case ctrl.ForeColor
Case Is = 16711680
strGTWY = ctrl.Caption
End Select
End If
Next

For Each ctrl In Me.Controls
If TypeOf ctrl Is TextBox Then
Select Case ctrl.Name
Case Is = strGTWY
strAlloc = ctrl.Value
End Select
End If
Next

Set db = CurrentDb
Set rsAllocHist = db.OpenRecordset("AllocationHistory", DB_OPEN_DYNASET)

rsAllocHist.AddNew
rsAllocHist("WWRNo") = Me!WWRNo
rsAllocHist("GTWY") = strGTWY
rsAllocHist("Allocation") = strAlloc
rsAllocHist.Update
rsAllocHist.Close

 

[TheAceMan1]

How are ya jw5107 . . .

Is it possible for [blue]more than one textbox[/blue] to equal a blue labels caption?

See Ya! . . . . . .

 

[PHV]

You wanted something like this ?
Dim cl As Control, ct As Control
Dim db As Database, rsAllocHist As DAO.Recordset

Set db = CurrentDb
Set rsAllocHist = db.OpenRecordset("AllocationHistory", DB_OPEN_DYNASET)
For Each cl In Me.Controls
If TypeOf ctrl Is Label Then
If cl.ForeColor = 16711680 Then
For Each ct In Me.Controls
If TypeOf ct Is TextBox Then
If ct.Name = cl.Caption Then
rsAllocHist.AddNew
rsAllocHist("WWRNo") = Me!WWRNo
rsAllocHist("GTWY") = cl.Caption
rsAllocHist("Allocation") = ct.Value
rsAllocHist.Update
End If
End If
Next
End If
End If
Next
rsAllocHist.Close

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886