Query / Function Problem.

[bgrave]

Hello People.
I have a Query with two columns "Num" & "SubjectList" the "Num"
column contains sequential No's starting at 1. This Query is entered into
the Row Source property of a List Box. What I want to do is when my Form is opened the list box will display entries No's 1 to 39 only. If under
the "Num" column in the Criteria row I enter >=1 and <=39 it all works and I get just what I want.
However what I would like to do is call a function
from the "Num" Criteria so that I can use calculated variables and I
cannot get my head round how to do this in code, any help would be appreciated.

 

[dkinley]

However what I would like to do is call a function
from the "Num" Criteria so that I can use calculated variables and I
cannot get my head round how to do this in code, any help would be appreciated.

I just want to make sure I have this right. You do not want to do some sort of function in the query, but you want to do something with this number after the user has selected it from the combo box?

If you want it to happen when they select something in the combo box, then you will need to put the code in the After Update event of the combo box. If you want it to happen when they click a button or somewhere else in the form, then you could do it on that controls event procedure.

I used the words need and could on purpose depending on what it is you exactly want in these scenarios. In either case, to refer the number that is sitting in the combo box, you would use Me.cboComboBoxName (change cboComboBoxName to whatever it is you have it named to).

For example, if you wanted something to happend depending on the value in there ...

Select Case Me.cboComboBoxName
 
Case 1 
    'do this if it is a 1
Case 2,3,
    'do this if it is a 2 or 3
Case 4 To 10
    'do this if it is 4, 5, 6, 7, 8, 9, or 10
Case Else
    'do this if it is none of the above
 
End Select

Here is a link to some VBA stuff to sorta assist: http://www.functionx.com/vbaccess/Lesson06.htm

If all of this is wrong and you want to do some sort of function stuff from the query you could create a module that the query could use to put some data into a global variable for manipulation somewhere else in your application.

If not, the type of manipulation you need might could be done inside the query in another column and you could refer to that value from the form if that value will only be used in that form (Me.cboComboBox.Column(x)).

It all depends on the scope of what you need it to do and/or where it needs to be done at.

-dK

 

[bgrave]

Hello thanks for your reply.
What I am doing is spread a large record set over three List boxes and I can do this by say hard numbering in the Criteria field of the "Num" column of the Query's IE >=1 and <=39 ,>=40 and <=79 etc. I am trying; totally without success to to do this in code because I would like to be able to calculate the value of the variables rather than setting distingt numbers.

 

[dkinley]

I think ... let me run through it again.

You want to do some calculations on something or the other. Then you want to take the result of these calculations and apply them as the criteria for the underlying query of a combo box?

-dK

 

[bgrave]

Hello dkinley. Really sorry not to have got back before but I had to be doing something else. Thanks for your input, with your input and others I seem to be able to move on to the next crisis. I had a Query with fields "Num" & "Subject" The "Num" column has sequential No's starting at 1 if I put >=1 And <=39 in the Criteria field of the "Num" column it all worked but as the database filled up these No's would have to change, this was my problem I could not get my head arround how to >=1 AND <= 39 in VBA code in a Function. It now works and thank you for your time.