Using Table as Key for Combo Box Data 1

[act2]

I would like to be able to set some defaults based on the week number by using a table. I'm not sure how I would go about doing it in VB any suggestions? Something like this

wk# Combo Box Value
3 Me.cbo.xxx1 7
3 Me.cbo.xxx2 14
3 Me.cbo.xxx3 21
3 Me.cbo.xxx4 28
3 Me.cbo.xxx5 35
3 Me.cbo.xxx6 42
4 Me.cbo.xxx1 7
4 Me.cbo.xxx2 21
4 Me.cbo.xxx3 14
4 Me.cbo.xxx4 28
4 Me.cbo.xxx5 42
4 Me.cbo.xxx6 35

    If Me.opDefault = True Then
    If wtb = 3 Then
Me.cbo.xxx1 = 7
Me.cbo.xxx2 = 14
Me.cbo.xxx3 = 21
Me.cbo.xxx4 = 28

    End If
End If
[\code]

 

[lameid]

You should be able to use the controls collection... Without checking syntax...

Me.Controls("ControlName").value = dblValue

Using something like that you ought to be able to pass the control name to the collection and get what you are after.

 

[act2]

Thanks for the reply Lameid. I'm not sure if I understand your reply and I don't think I stated my question very well. I’m fairly new to programming in VB. Let me try to explain my problem again. I have a form that has combo boxes that I want to set a default value based on the estimated number of weeks to build a project. I would like to put the default values in a table and fill in the value based on the weeks to build (wtb). Is there VB code that will look up value in a table and return the associated value? Thanks


Table
wtb# Combo Box Value
3 Me.cbo.xxx1 7
3 Me.cbo.xxx2 14
3 Me.cbo.xxx3 21
3 Me.cbo.xxx4 28
3 Me.cbo.xxx5 35
3 Me.cbo.xxx6 42
4 Me.cbo.xxx1 7
4 Me.cbo.xxx2 21
4 Me.cbo.xxx3 14
4 Me.cbo.xxx4 28
4 Me.cbo.xxx5 42
4 Me.cbo.xxx6 35

    If Me.opDefault = True Then
    If wtb = 3 Then
Me.cbo.xxx1 = 7
Me.cbo.xxx2 = 14
Me.cbo.xxx3 = 21
Me.cbo.xxx4 = 28

    End If
End If

 

[PHV]

Have a look at the DLookUp function.

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

 

[act2]

Not sure if I could use Dlookup to get what I want. Currently I have a start date and complete date on the form. I used DateDif to get the weeks to build. I would like the code to take the weeks to build (wtb) number and compare it to my table and if there are matches return all of the matches in to the appropriate combo-box. I.e. if the weeks to build (wtb) was 4 then I would like the value of combo-box Me.xxx1 to = 7, Me.xxx2 = 21. Hope this makes sense.

Table
wtb# Combo Box Value
3 Me.cbo.xxx1 7
3 Me.cbo.xxx2 14
3 Me.cbo.xxx3 21
3 Me.cbo.xxx4 28
3 Me.cbo.xxx5 35
3 Me.cbo.xxx6 42
4 Me.cbo.xxx1 7
4 Me.cbo.xxx2 21
4 Me.cbo.xxx3 14
4 Me.cbo.xxx4 28
4 Me.cbo.xxx5 42
4 Me.cbo.xxx6 35

 

[lameid]

It sounds like you have related information that should be what is called normalized. Check out

http://www.datamodel.org/NormalizationRules.html

for the rules of data normalization. The first 3 rules are the easiest and also the most important to follow. You can get often get away without doing anyting else and in some cases using higher level rules is undesirable for various reasons.

Using Normalization of course assumes that you undestand joins in queries.

As for a how to on how to accomplish what you asked, in a nutshell you want to use a recordset...

Dim cnn As New ADODB.Connection
Dim RS as New ADODB.Recordset

Set cnn = CurrentProject().Connection
RS.Open "Select * From Table where [wtb#] = " & Me!WTB, cnn, adOpenDynamic, adLockOptimistic

While Not RS.EOF
Me.Controls(RS![Combo Box]).value = RS![Value]
Rs.movenext
Wend



What's important above the aboce code is that Table in the RS.Open table is the name of the table in your post that holds the names of the combo boxes. Also you show your table having ME! in the combo box field... You will either need to change the Me.Contols line to either reference a new field or change the data to just contain the name of the control and not the me!

I wrote this code off the cuff, so there might be a syntax error or two. But hopefully it works, if not post with the line in error and a specific error message description.

 

[act2]

Lameid, Just got back from a trip and saw your reply. This is exactly what I wanted to do. The code worked great after making the adjustments you suggested. I’m in awe of the talent on this site. Thanks again.