Use DMax with Combo Box Column Property 1

[sterlecki]

After a user selects a series of Combo boxes a final combo box may or may NOT be populated depending on the data set

The following code is my attempt to do the following when a command button is clicked:

If the list is not populated then set the control
cbo_TestNumber to 1

If the list is populated then find the DMax "TestNumber" from the list, increment by 1 and set this value to cbo_TestNumber.

'Select Test Number Automatically

Dim ListControl As Control
Dim count As Long
Set ListControl = Me.cbo_TestNumber
With ListControl
If .ListCount = 0 Then
Me.cbo_TestNumber = 1
Else
'Can't get this to work using column property
count = DMax"TestNumber", "data_GroupData", " [TestNumber] = Forms![frm_TestInput]![cbo_TestNumber].Column(1)")

'
'works fine using a query
count = DMax("TestNumber", "qry_MaxTestNum")

End If
count = count + 1
Me.cbo_TestNumber = count
Me.Refresh
Debug.Print count
End With

Using a query I can get this to work fine but I would like to use the column property and can't seem to get this run

 

[randy700]

Are you sure your using the right column number?
The first column is cbo_TestNumber.Column(0)


Randy

 

[PHV]

What is the SQL code of qry_MaxTestNum ?
Your code with the Column property makes no sense to me as it is supposed to return the max of a FIXED value ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sterlecki]

Randy,

The error I received did Not appear to be related to the column number which is in fact (0) not (1)

The error I receive is:

Undefined Function 'Forms![frm_TestInput]![cbo_TestNumber].Column' in Expression

PHV,

The SQL for the query that works is:

SELECT data_GroupData.TestNumber, data_GroupData.GroupNameID, data_GroupData.TestYear, data_GroupData.WellTestPad, data_GroupData.LeaseNumber
FROM data_GroupData
WHERE (((data_GroupData.TestYear)=Forms!frm_TestInput!cbo_TestYear) And ((data_GroupData.WellTestPad)=Forms!frm_TestInput!cbo_WellTestPad) And ((data_GroupData.LeaseNumber)=Forms!frm_TestInput!cbo_LeaseNumber))
ORDER BY data_GroupData.TestNumber;


If I understand what you're saying then using the Column property in this way is not acceptable?

The query that I used (see SQL above) seemed to work just fine on initial test. It read the max value of the TestNumber based on the criteria of the various combo boxes and incremented just fine for the addition of new records.

I just thought using 1 line of VBA was easier than storing a query for this purpose.

 

[PHV]

count = DMax("TestNumber", "data_GroupData", "TestYear=Forms!frm_TestInput!cbo_TestYear And WellTestPad=Forms!frm_TestInput!cbo_WellTestPad And LeaseNumber=Forms!frm_TestInput!cbo_LeaseNumber")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sterlecki]

PHV,

Worked like a charm. It looks so easy when you know what you're doing. Thanks for the lesson.

A star for you!