Full Version: They come like buses - another code problem
UtterAccess Forums > Microsoft® Access > Access Forms
liquidmetal
Just as I fix one problem another drives me crazy.
I have a combo box control (PropID) which is populated by row source query with several fields from about 4/5 tables and includes amongst others these fields. In order for the items to be shown on the drop down list the conditions must be:
Fit for Let
Void
CS=Yes
The problem I have is that when a property is selected it is marked as occupied and hence is no longer void and disappears from the selection.
I want to keep this selection so put this code into the on current event of that form:
Private Sub Form_Current()
Dim strSQLVoid As String
Dim strSQLAll As String
strSQLVoid = "SELECT tblProperty.PropID, tblProperty.PropAddress, tblLL.LLName, tblLL.LLMobile, tblLL.LLTelephone, tblProperty.PropAddress, tblPropStatus.PropStatus, tblPropStatus.CS, tblSurveyDetails.FFL FROM (((tblLL INNER JOIN tblProperty ON tblLL.LLID=tblProperty.LLID) INNER JOIN tblPropStatus ON tblProperty.PropID=tblPropStatus.PropId) INNER JOIN tblSurvey ON tblProperty.PropID=tblSurvey.PropID) INNER JOIN tblSurveyDetails ON tblSurvey.SurveyID=tblSurveyDetails.SurveyID WHERE (((tblPropStatus.PropStatus)=Void) AND ((tblPropStatus.CS)=Yes) AND ((tblSurveyDetails.FFL)=Fit For Let)) ORDER BY tblLL.LLName, tblProperty.PropAddress"
strSQLAll = "SELECT tblProperty.PropID, tblProperty.PropAddress, tblLL.LLName, tblLL.LLMobile, tblLL.LLTelephone, tblProperty.PropAddress, tblPropStatus.PropStatus, tblPropStatus.CS, tblSurveyDetails.FFL FROM (((tblLL INNER JOIN tblProperty ON tblLL.LLID=tblProperty.LLID) INNER JOIN tblPropStatus ON tblProperty.PropID=tblPropStatus.PropId) INNER JOIN tblSurvey ON tblProperty.PropID=tblSurvey.PropID) INNER JOIN tblSurveyDetails ON tblSurvey.SurveyID=tblSurveyDetails.SurveyID WHERE ((tblPropStatus.CS)=Yes) AND ((tblSurveyDetails.FFL)=Fit For Let)) ORDER BY tblLL.LLName, tblProperty.PropAddress"
If Me.NewRecord Then
Me.PropId.RowSource = strSQLVoid
Else
Me.PropId.RowSource = strSQLAll
End If
End Sub
But everthing is blank and get an error message:
Syntax error (missing operator) in query expression '((tblPropStatusCS)=Yes) AND ((tblSurveyDetailsFFL)=Fit For Let))'
Thank you.
jwhite
Possible quick fix: Replace =Fit For Let with =[Fit For Let]
Not good design to include spaces (or special characers) in object names. The brackets tells Access that which is within is an object name.
liquidmetal
Hi John
Thanks for the reply but no dice!
Oput Void into brackets like [Void] as well as [Fit for Let] but get another error message:
Extra) in query expression '((tblPropStatus.CS)=Yes) AND ((tblSurveyDetails.FFL)=[Fit for Let]
I also put the brackets round yes as in [Yes] but same thing and the drop down stays resolutely blank.
Help!
AC2Designs
I think you only need brackets if you are referring to a Field or Object name????

Oalso do not think you need all the parentheses after the Where statement

Also, do you need apostrophe's around the values:
'Yes' 'Void' and 'Fit For Let'

Kevin
Edited by: kheilmann on Wed Aug 15 12:12:10 EDT 2007.
liquidmetal
Kevin
Thanks for taking time out but again no luck.
Oenclosed yes, void and fit for let with single apostrophes 'Void' and no change and when i do it in double apostrophes as in "Void" I get a compile error immediately!
The combo is blank and get the same error message as posted above (2nd reply), Also counted the brackets () and seems fine - not too many.
what am I missing?
AC2Designs
Have you tried it without the parentheses? I've never used them cause they always give me trouble..

Also,in the strSQLALL statement you are missing an Open Parenthesis in the WHere statement...

I want be wrong, but I do believe you'll have to have those apostrophes?? Are they not required when
referring to string expressions??

Kevin
Edited by: kheilmann on Wed Aug 15 13:03:48 EDT 2007.
AC2Designs
One more thing..
Why can't you setup your recordsource as the strSQLALL at all times?
Apparently it does not matter if Void is there or not, if you are putting records back after removing
the VOID criteria, then why have the VOID criteria at all. ????
Just wondering..
Kevin
AtomicWaste12
The conditions for this combo box if for items to be listed that meet the conditions:
Are void
Fit for Let
CS=Yes
However once a property is taken by a tenant the property is marked as occupied and no longer is Void hence it disappears from the list but it also disappears from the selection ie it goes blank hence this code...
jwhite
Okay, I'm at home now relaxing (?) sad.gif

Not trying to understand your logic with two different SQL statements -- for now just to get you past the compile error...

Part of Variable strSQLAll that looks incorrect:
WHERE (((tblPropStatus.PropStatus)=Void) AND ((tblPropStatus.CS)=Yes) AND ((tblSurveyDetails.FFL)=Fit For Let))

Part of Variable strSQLAll that looks incorrect:
WHERE ((tblPropStatus.CS)=Yes) AND ((tblSurveyDetails.FFL)=Fit For Let))

Void
Fit for Let
CS=Yes

By looking at the SQL, I see [CS] is a fieldname in table [tblPropStatus]. Looks like it is a Yes/No field?
If so, you should evaluate for =True, not =Yes.

What is "Void" ? Table field name, control name, or variable? What DataType is the field [PropStatus] ?

What is "Fit for Let" ? Table field name, control name, or variable? Whatever it is, it needs brackets around it. What DataType is the field [FFL] ?

Yes, you do have an extra ( immediately after the WHERE in strSQLVoid=, or accidentally removed the close-parenthesis before ORDER BY -- Fix it by adding a ) before ORDER BY. How did you build these two SQL statements? Manually or copy from a stored Query in the Query Builder?

I'm also curious why you have so many columns in the combobox? To use a ComboBox to select a Primary Key of a record to display/process, normally you would have just the PK field and 1-2 field to help define the selection in the dropdown.

Answering these questions will allow us to help you better.
liquidmetal
Hi John
Thanks for the long reply. I very much appreciate your help.
I must admit I copied this from the query builder in recordrow source.
I just deleted the the void part for the sqlAll part of the code.
CS is a check box control = Yes or No.
(tblPropStatus.PropStatus)=Void is part of combo box selection and hence is a field - Text.
((tblSurveyDetails.FFL)=Fit For Let)) is also part of a combo box selection hence is a field and is also Text.
I have multiple columns but only the address, landlord and his mobile/tel details are visible to the user the others are to order and filter the drop down list which fulfill the criteria that the property is available, fit for let, and CS=True.
I had enclosed these fields in double and single apostrophes but get compile error or the other error respectively. I have also enclosed them in the square brackets [Yes] but same error.
And the drop down is all blank!
Any ideas are gratefully received.
Thanks
liquidmetal
An update:

Omade the changes john suggested and hey the list is populated but does not filter out the occupied properties.

I have re read the post above and can see why some are questioning the need for the SQLAll code, however I had been advised in the past to use this method for what I am trying to achieve. Which is:

Mr Jones wants to view property a 3 bedroom property in area T.
We look in the drop down list and find there is a 3 bed property (X) in area T that is void and Fit for Let.
This property is selected.
Mr Jones likes the property and takes the tenancy.
The property is now marked as Occupied
and hence should no longer be available for Lets in the drop down combo box.
However when a property is marked as occupied and the criteria in the query is void it does diappear from the list but also is not visible as the selected property X for Mr Jones ie When I look at the Tenancy record the box does not have the property X.

Hence this code was suppose to disappear non Void properties from the list of avialable properties but keep the selected and taken property in the combo control in the record.

I hope this clarifies it further.

My code looks like this now:

Private Sub Form_Current()
Dim strSQLVoid As String
Dim strSQLAll As String
strSQLVoid = "SELECT tblProperty.PropID, tblProperty.PropAddress, tblLL.LLName, tblLL.LLMobile, tblLL.LLTelephone, tblProperty.PropAddress, tblPropStatus.PropStatus, tblPropStatus.CS, tblSurveyDetails.FFL FROM (((tblLL INNER JOIN tblProperty ON tblLL.LLID=tblProperty.LLID) INNER JOIN tblPropStatus ON tblProperty.PropID=tblPropStatus.PropId) INNER JOIN tblSurvey ON tblProperty.PropID=tblSurvey.PropID) INNER JOIN tblSurveyDetails ON tblSurvey.SurveyID=tblSurveyDetails.SurveyID WHERE (((tblPropStatus.PropStatus)='Void') AND ((tblPropStatus.CS)=True) AND ((tblSurveyDetails.FFL)='Fit For Let')) ORDER BY tblLL.LLName, tblProperty.PropAddress"
strSQLAll = "SELECT tblProperty.PropID, tblProperty.PropAddress, tblLL.LLName, tblLL.LLMobile, tblLL.LLTelephone, tblProperty.PropAddress, tblPropStatus.PropStatus, tblPropStatus.CS, tblSurveyDetails.FFL FROM (((tblLL INNER JOIN tblProperty ON tblLL.LLID=tblProperty.LLID) INNER JOIN tblPropStatus ON tblProperty.PropID=tblPropStatus.PropId) INNER JOIN tblSurvey ON tblProperty.PropID=tblSurvey.PropID) INNER JOIN tblSurveyDetails ON tblSurvey.SurveyID=tblSurveyDetails.SurveyID WHERE (((tblPropStatus.CS)=True) AND ((tblSurveyDetails.FFL)='Fit For Let')) ORDER BY tblLL.LLName, tblProperty.PropAddress"
If Me.NewRecord Then
Me.PropId.RowSource = strSQLVoid
Else
Me.PropId.RowSource = strSQLAll
End If

End Sub






Edited by: liquidmetal on Thu Aug 16 8:38:36 EDT 2007.
liquidmetal
By the Way

Ocopied the new code from one I did earlier which does work in another housing DB:

Private Sub Form_Current()

Dim strSQLVoid As String
Dim strSQLAll As String

strSQLVoid = "SELECT tblProperty.PropertyID, tblProperty.PropertyAddress, tblProperty.PropertyArea, tblProperty.PropertyPostCode, tblProperty.PropertyType, tblProperty.PropertyBedrooms, tblProperty.PropertyLivingRooms, tblProperty.PropertyRent, tblProperty.PropertyFFL, tblProperty.PropertyVoid, tblProperty.PropertyEarmarked FROM tblProperty WHERE (((tblProperty.PropertyFFL)=Yes) AND ((tblProperty.PropertyVoid)=Yes) AND ((tblProperty.PropertyEarmarked)=No))ORDER BY tblProperty.PropertyAddress"

strSQLAll = "SELECT tblProperty.PropertyID, tblProperty.PropertyAddress, tblProperty.PropertyArea, tblProperty.PropertyPostCode, tblProperty.PropertyType, tblProperty.PropertyBedrooms, tblProperty.PropertyLivingRooms, tblProperty.PropertyRent, tblProperty.PropertyFFL, tblProperty.PropertyVoid, tblProperty.PropertyEarmarked FROM tblProperty WHERE (((tblProperty.PropertyFFL)=Yes) AND ((tblProperty.PropertyEarmarked)=No))ORDER BY tblProperty.PropertyAddress"

If Me.NewRecord Then
Me.PropertyID.RowSource = strSQLVoid
Else
Me.PropertyID.RowSource = strSQLAll
End If

If Not Me.NewRecord Then
Me.PropertyID.Locked = Me.Accepted
Me.ViewDate.Locked = Me.Accepted
Me.ViewTime.Locked = Me.Accepted
Me.VieiwngOfficer.Locked = Me.Accepted
Me.Comment.Locked = Me.Accepted
End If
End Sub


The diffrence in this db was that the controls were all check boxes ie Yes/No for FFL, Void and Earmarked

In my new db there is onky one check box - CS=Yes/No while the status is a combo box selection which can be void, occupied or earmarked. Text.

SurveyStatus.FFL is also a combo selection= Fit for Let or Not Fit For Let. Text.

I hope this throws more light on my current problem.
AC2Designs
Do you get error messages with both new records and existing ones?
That error do you get when both events occur?
ie
If Me.NewRecord Then
Me.PropertyID.RowSource = strSQLVoid
Else
Me.PropertyID.RowSource = strSQLAll
End If
Also, the CS field in tblPropStat can be set to be analyzed by True/False, Yes/No and I think something else...
Make sure the value in the SQL statement matches what the table is set to...I'm guessing since it worked with
YES in the other DB with CheckBoxes, then it should probably evaluate as YES in the new DB as well.
I think anyway.....
liquidmetal
Thanks for getting back.
Sorry for the late reply - was training someone and took longer than anticipated.
In my old code there was no error and it worked very well. It did what I wanted :-)
In my new code I do not get any more error messages - no dialog boxes that state problem with missing operators or extra ) in the SQLAll code which was fixed.
The error or problem is that it is not filtering out not Void (ie occupied or earmarked properties).
HAs I stated above the only diffrence is that this is
1).........A Multi Table Query (as you can see) the old one only had 1 table.
2).........The status and FFLs are Text fields in their own tables.
If I enclose Fit for Let and Void in double apostrophes " " I get an immediate compile error.
I do not get that problem using single apostrophes ' '.
But I also do not get the results I want - a filtered list that shows only void, fit for let, cs=yes properties.
Instead I get all fit for let CS=yes properties in the list.
I do get the selected property staying in the combo box but obviously that could be due to all properties being in the list.
So any new ideas would be very helpful.
AC2Designs
OK. When a form is first opened the Current Event is triggered.
Since it does not open to a new record (I assume) the SQLALL string will accepted
ie.
If me.newrecord then
me.propid.recordsource = strSQLVOID
else:
me.propid.recordsource = strSQLALL
end if
So when a form is opened it will set the Recordsource for the PropID combo to strSQLALL.
I want very well be confused, but should the conditions be swapped above?
ie.
if me.newrecord then
me.propid.recordsource = strSQLALL
else:
me.propid.recordsource = strSQLVoid
end if
That way when you open the form and navigate through existing records you see the records where
CS = YES
VOID
Fit For Let
Then when you create a new Record you get records where
CS = YES
Fit For Let
FOr do I have your logic backwards?
Kevin
liquidmetal
Kevin

by swapping the code around as u suggested.

I thought it had done the trick but no.
It is filtering for void now but also losing the selected property from the drop down box. I also need to keep the property selected in the tenancy record.

Edited by: liquidmetal on Thu Aug 16 13:01:07 EDT 2007.
AC2Designs
Seems there is a bit of logic to work out...
I am a little confused. Sorry, it happens fairly often. sad.gif
When the form 1st opens you want to see all records in the PropID combo where
CS = YES
VOID
Fit For Let
After creating a new Record you want to remove the VOID criteria from the WHERE statement:
This will basically keep the VOID propstatus records, but will also add every other propstatus record
ie. "occupied" or "ear marked"..
Correct?
Now dealing with the strSQLVOID statement:
When a property is selected from a comboBox (name?) it is being removed from the list because
it is no longer VOID? Is that right? Is this EVENT in addition to the strSQLVOID situation? In other words,
you really have 3 events to deal with, right?
me.NewRecord = Set strSQLALL
Not Me.NewRecord initial list = Set strSQLVOID
Not Me.NewRecord after a property is selected from another combobox = Set strSQLVOIDplusSelectedProp
Is this correct?
Am I on the right track? I'm just having trouble figuring out your sequence and logic....
Kevin
jwhite
With all the swapping around of field/variable names and copying over SQL statements previously given, might as well let Kevin finish helping you. Please pardon me for saying this again, but its better to get one thing working correctly before jumping around something else that is wrong...
That has made this confusing is you are changing things without having a recommendation to do so, and so we don't know what your current code actually is. Also, forget about the apostrophes and quotes as stated above -- bogus. The part where these WOULD come in to play is if you have controls on your form that you want to be included in the SQL statement that is executed.
Let's say that [Void], [CS] and [Fit for Let] are controls on your form (Note again that brackets are only needed if you have spaces in your object names -- used here on all three for clarity of this message). In your SQL, it seems that all three compare to fields in tables that are Yes/No DataType, which when evaluating by code is compared against True/False.
If the selection of True/False is controlled by the code with criteria that will never change, and not the User, then stay with what you have. If you want the user to have control to select the criteria (Note: using proper/consistant naming conventions for the controls):
CheckBox: Name = chkVoid
CheckBox: Name = chkCS
CheckBox: Name = chkFitforLet
Allow user to Check or UnCheck these controls. They will have a value of True or False.
Now, the WHERE part of one (?) of your SQL would be:
"WHERE (((tblProperty.PropertyFFL)=" & chkFitforLet & ") AND ((tblProperty.PropertyVoid)=" & chkVoid & ") AND ((tblProperty.PropertyEarmarked)=False))ORDER BY tblProperty.PropertyAddress"
> If I enclose Fit for Let and Void in double apostrophes " ",
> I get an immediate compile error.
> I do not get that problem using single apostrophes ' '.
> But I also do not get the results I want...

That is proper behavior in both cases. Note of clarification here - Apostrophe = ', Quote = " (what you are calling double-apostrophe). You get the error when using quotes because you have quotes within a quoted string. When you use apostrophes, e.g. 'Void', you are checking the table in the field if it is = to the characters "Void" (w/o quotes), not True as you may think... Hence the WHERE example above, which will properly evaluate for the True/False condition of each field.
You are trying hard -- maybe too hard. You are making it hard on yourself by randomly making changes without having the knowledge of what the change will cause to happen. Maybe one of these links will help you better understand building Queries in Code and the use of apostrophes and Quotes:Please take a little time and look these over before continuing with your code. It's better to teach someone than just give them the answer. I think you will find the answers in no time. sad.gif Good luck!
liquidmetal
Hi kevin

Apologies for taking so long to get back to you. I have been really busy this morning with reports and other things.

Yeah swapping the code did filter for voids but did not keep the selection in the combo box PropID. As mentioned I followed the logic and code I had been recommended previously. The old code has been added in one of my replies above. That code works!

So following the same logic the new code should also do the same thing.

However there are differences between the old working code and the new non-working code:

1........The new code is multi-table.
2........The (tblPropStatus.PropStatus)='Void' is a combo selection (3 choices only: void, occupied, earmarked). Text field.
3........The (tblSurveyDetails.FFL)='Fit For Let' is a combo selection (2 choices only: Fit for let, Not fit for Let). Text field.
4........The (tblPropStatus.CS)=Yes is a check box (Yes/No).

in the old code

Void=check box (Yes/No)
FFL=check box (Yes/No)
Earmarked=Check box (Yes/No)

Is this difference causing the problems?

Omean I followed the same logic put in the text field in ' Void ' and 'Fit for Let' and CS=Yes for SQLVoid

For SQLAll I removed the 'Void' part and left in 'Fit for Let' and CS= Yes.

Surely if I have done the same thing and followed the same flow... then it should do the same thing.
liquidmetal
John
Thanks for the reply - lots of reading but much appreciated.
jwhite
Hmmmm...... Now confused even more... sad.gif
Is there any chance you could post a working copy of the MDB -- just enough to keep the components that are being referenced here and still be able to run the forms/queries? Compress it into a WinZIP file and attach it here to a message (you attach after you click 'continue' by clicking the BROWSE button) -- under 500k.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.