Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

QBF results from listbox to form problem

Status
Not open for further replies.
weezles

weezles

Technical User
Jul 18, 2003
81
GB
hi

I’m using the following which uses a QBF and puts the results in a listbox. I then select which items I wish to view in form view. This code works fine when I’m only looking for StaffNo which is the PK but I also want to match another two fields. Can anyone suggest what is wrong?

Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In lstBox.ItemsSelected
stDocCriteria = stDocCriteria & "[StaffNo]=" & lstBox.Column(0, VarItm) & " AND "
stDocCriteria = stDocCriteria & "[PayPeriod]=" & lstBox.Column(1, VarItm) & " AND "
stDocCriteria = stDocCriteria & "[Week]=" & lstBox.Column(2, VarItm) & " OR "
Next

If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else

stDocCriteria = "True"
End If

GetCriteria = stDocCriteria
End Function

Thanks in advance

Lou
 
Sort by date Sort by votes
Hi

It would haev been helpful if you had told us the symptom of it not working eg error message(s) or whatever

but at a guess, are some of the colums strings?, in which case you need quotes in the expresion for example

Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In lstBox.ItemsSelected
stDocCriteria = stDocCriteria & "[StaffNo] = " & lstBox.Column(0, VarItm) & " AND "
stDocCriteria = stDocCriteria & "[PayPeriod] = '" & lstBox.Column(1, VarItm) & "' AND "
stDocCriteria = stDocCriteria & "[Week] = '" & lstBox.Column(2, VarItm) & "' OR "
Next

If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else

stDocCriteria = "True"
End If

GetCriteria = stDocCriteria
End Function



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Thanks Ken

I have added this but my problem seems to be that it cant find the fields PayPeriod or Week as is is coming up with a text entry box for each.

The fields it is searching for are on a sub form and StaffNo is actually the FK from the subform not the PK as i said earlier.

It is finding the correct record on the main form but not the correct records on the sub form. Even though it is looking for the FK and not the PK.

Even when i type the values in the text entry boxes it doesn't get the right record on the subform. Any suggestions on how to do this?

Thanks

Lou
 
Upvote 0 Downvote
Hi

To reference (say) staffNo on a subform, from the main form you need:

Me.MySubFormControlName.FORM.[StaffNo]

where MySubFormControlName is the name of the sub from CONTROL not (necessarilly) the sub form

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Hi ken

I've tried referencing the fields like you suggested (and a whole load of other ways) but it still cant find anything from the subform. Any other suggestions?

Lou
 
Upvote 0 Downvote
Hi

Your first post implied that your method worked ok with one column ie the Prime Key staffId

Is this so?

From the code you have shown it appears you are building a criteria string to be used in an OpenForm or OpenReport command am I right?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Hi

Are you trying to show the results in a sub form?, if yes, have you executed a requery on the subform control?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Hi

I am building a criteria string to open a form that contains a subform. I does work ok with StaffNo which is the PK of the main form, but not after adding the extra single quotes. It is not finding anything from the subform.

Sorry for all the confusion, i'm working with someone else's code and its driving me insane!

Lou
 
Upvote 0 Downvote
Hi

OK, point 1, you only need the quotes if the column is defined as a string, since StaffNo worked OK, without the quotes, I assume it is not a string, so you do not need the quotes

Point 2, if StaffNo is the PK of the recordsource of the form you are opening, then setting the criteria StaffNo=??? whatever will open the form filtered for that record, but you cannot use that technique to filter the sub form on the main form. The Rows which show in the sub form are dependant either on the query on which the sub fornm is based, and/or on the mastchild link fileds defined for teh subform control so first have you got the master child fields declared in the sub form control ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
hi

yes the master/child fields are declared
 
Upvote 0 Downvote
Hi

Ok, so what I am saying is that it would appear the PayPeriod and Week are not relevant to the main form, so they should not be passed in the criteria to open that form

I assume the master child criteria is the StaffNo?

So where do PayPeriod and week come into it, and how if at all do you expect the sub form content to be restricted?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
hi

yes the master/child is StaffNo. My list box holds StaffNo, PayPeriod and Week as columns entered by a query by form. I thought i could just add these in as criteria to pass to the function to open my form

lou
 
Upvote 0 Downvote
Hi

Yes you can PASS then in the criteria, but waht do you expect Access to do with them?, they appear to be irrelevant to the selection of the row(s)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
I want access to open the correct record in the main form with the sub form at the correct record for the pay period and week. Each staff member can have many periods each with 4 weeks.

Lou
 
Upvote 0 Downvote
Hi

OK I thought as much

Putting the Pay period and week in the criteria of the main form will not achieve your object

you need to pass the values to the main form, perhaps using OpenArgs property (see help), then put code (probably in the on open even of the main form) to position on the correct record in the sub form, see recordsetclone in help

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
hi

thanks for your help ken, and your patience.

lou
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

breezett93
  • Question
Converting from 32bit to 64 bit. After converting, database takes significantly longer to open 1
Replies
14
Views
876
dhookom
dhookom
Zyborg
  • Locked
  • Question
Using a variable as reference to a form
Replies
5
Views
665
Zyborg
Zyborg
Moss100
  • Locked
  • Question
Concatenation problem 3
Replies
5
Views
1K
Moss100
Moss100
Moss100
  • Locked
  • Question
Filter vba problem
Replies
1
Views
673
Andrzejek
Andrzejek
V
  • Locked
  • Helpful tip
Capture Shift Key if Pressed on A form
Replies
3
Views
587
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top