Full Version: Can't Find What I Broke
UtterAccess Forums > Microsoft® Access > Access Forms
KenThompson
Hello All,
I have a search form on my db with two searches. One works fine, the 'between date' search, however, I seem to have broken. My live db works fine, but I've made a copy in order to standardize the buttons and now that my buttons are standardized I was running through everything before copying it back to live and found this problem.
It works by setting a username, start date, and end date. Click search which runs this code:
!--c1-->
CODE
Private Sub Date_Search_Click()
DoCmd.OpenForm "Search Project Query Res", acNormal
DoCmd.Close acForm, "SelectProject", acSaveYes
End SubForm

'Search Project Query Res' has a query as a record source, which has a few fields, and criteria on a date field with a between statement.
What is happening is the enddate field doesn't seem to be making it through suddenly. The results form has this code:
CODE
Private Sub Form_Load()
Dim ProgNumb As Variant
Dim rsTemp As New ADODB.Recordset
ProgNumb = [Forms]![SelectProject]![cntrlProg]
rsTemp.Open "SELECT [txtEmpLastName] as LName, [txtEmpFirstName] as FName FROM tblEmployee Where [pkID] =" & ProgNumb & ";", _
        CurrentProject.Connection, adOpenStatic, adLockReadOnly
Me.NameBox = rsTemp("FName") & " " & rsTemp("LName")
Me.SDate = [Forms]![SelectProject]![SDate]
Me.EDate = [Forms]![SelectProject]![EDate]
If Me.SearchRes.ListCount = 0 Then
   Me.Label13.Caption = "No Records found for "
   Me.Label13.Width = 2016
   Me.NameBox.Left = 2580
   Me.btnClose.Visible = True
   Me.btnTry.Visible = True
End If
End Sub

And so the results form displays with no reuslts and NO END DATE. I put a msgbox in to show the [Forms]![SelectProject]![EDate] and it errors out with a null. I have looked thoroughly at [SelectProject]![EDate], flipping back and forth between properties for it and SDate and can find no differences.
********************************************************************************
**************
I've put this on another forum and have only gotten the sound of crickets. So, as I find a lot of answers on this forum when I google stuff I thought I'd try here. And here is more info, which I added as an update on the other forum.
********************************************************************************
**************
Ok, I haven't been able to find anything and have gotten no responses so I decided to make a new copy and start over. This time I started with the offending form. Ran the straight copy and ran it after every change until it broke again.
So my 'button standardization process' is
1 Create a label
2 Type same caption on 'label button' as was on button being replaced.
3 Set Format properties (height, SFX, Text Align) to be same as all the other 'label buttons' I'm creating
4 Copy 'replaced button's' Name value from other Tab
5 Add "2" to end of 'replaced button's' Name value
6 Paste Name from 'replaced button' into new 'label button's' Name value
7 Change 'label button's' On Click event to [Event Procedure]
8 Click on ellipses to verify cursor goes to pre existing 'replaced button's' function
9 Delete 'replaced button'
10 Move new 'label button' into proper position
So, I ran it between each step and it worked until I deleted the Button (step 9). I've doen this with about 20 to 30 other buttons in the app and they all work fine.
********************************************************************************
****************
So, any ideas on what the problem is?
Thanks
Ken
bulsatar
welcome2UA.gif
o you have a copy of the db that you can upload? You would first have to send it to a zipped file, fyi.
If not, it sounds like you might not be addressing the textcontrols correctly from the other form.
KenThompson
Hhhmmm, I could possibly upload the frontend but def not the backend. And I may have the company SWAT team drop out of the ceiling if I do that, things are pretty tight around here. Will just the FE help?
eally the second form not having the date isn't the crux of the problem, but just a clue as to what the problem is. It's the query returning no results when it should that is the problem, I don't know how to do something like 'breakpoint' the query to see what values it's working with so I'm assuming that it is having the same (some side effect of deleting the original button) problem as the resulting code... which is having no EDate for it's criteria: Between [Forms]![SelectProject]![SDate] And [Forms]![SelectProject]![EDate]
This is totally baffling and holding things up, so I guess for the time being I am going to just make the mystery button invisible and leave it on there. I'd like to figure this out though, so let me know if I should try to upload the FE.
Thanks for your response,
Ken
KenThompson
Update:
If I use the same process to revert back to a button (on the same copy of the form where a button has been completely replaced by a 'label button' and functionality is gone)... i.e. just create a button, change name of 'label button', name button corresponding to VBA sub, point ON Click event to proc.... the button works as expected. (expected being all dates get thru to called form, query generates results)
I guess I have to start with a new copy though, as when I try to switch the functionality back to the 'label button' (expecting it to work since it now has a working button... mmm, (nearby?) and it works before deleting the original button) so I can invisible-ize the button... it still doesn't work.
Either I've inadvertantly initialized the Malicious Evil Access Object or Mr. Mxyzptlk has entered the scene.
bulsatar
Yes, uploading the front end will help a lot. For the back end, you can delete all of the data and maybe put a row or 2 of fake data in would be fine. We will need that to verify your query works correctly. Stripped down copies of each with only the relevant 2 forms and the data deleted backend tables would be fine also.
KenThompson
OK, here it is. I'm uploading it in the broken state. The form SelectProject is the culprit. You'll see on it the button SearchBtn2 with the caption "Button" is deactivated, and the label SearchBtn is on but doesn't work correctly. To see it work correctly just swap the names between the two on the Other tab. To use the form open Index or Admin Index and click on "Find Plan" (for some reason this form just comes up blank if you switch from design to form view).
've commented out the code in Index to check user ID and decide which Index you should see... but I haven't commented out any other userID code, like when adding a new plan or element, so they won't work for you.
I've simply put the button over the top of the label and made the button transparent, so it works and looks right at the moment. But if you share my curiousity about this then I'd appreciate you having a look and telling me whats up. (I don't have any mangos)
bulsatar
Would you be willing to save it as a 2003? I don't have 2007 or 2010...sorry
KenThompson
Okie Doke, here's 2003 version
bulsatar
Your date problem was because you were using a Label for the search button. A label cannot have focus so when you pushed search, the textbox did not update it's value property with the new date. Change your all your buttons from labels to a button or an image.
The next part is, what are you using the rsTemp for? I don't see you actually using it anywhere, however it would be a good idea to replace the SearchRes query with a dynamic sql statement. Much more versatile! An example would be:
SearchRes.RowSource = "SELECT [txtEmpLastName] as LName, [txtEmpFirstName] as FName FROM tblEmployee Where [pkID] =" & ProgNumb & " ;"
and this would be added just above the If statement in your Form_Load code.
Hope that helps <
KenThompson
OK, that makes sense. I just had a tab issue with another form and validation, it was considering the last control selected as empty because my label button wasn't taking the tab away. That was more obvious as to what was going on, I should have seen the correlation to this issue. So, since the whole exercise that caused this problem was updating the buttons to the better looking labels, I will stick with my solution of a transparent button functioning over the top of a visible label. Moves the tab for the instances where this is an issue, and makes all the buttons look better than the ugly system buttons that the command buttons are stuck with.
I use the rstemp to put the name that was searched for at the top of the results. I used the dates from the fields on the select form, but the name lookup is off a query and will only give me a key so I have to do the lookup somehow. I am not slick at all with access, I just code so I got assigned this task of building this interface. I'm sure I have a lot of stuff that is not the best way to do it, but just a way I found that works.
Odon't really understand what you are saying about the problem with the query. The select statement you show is not what goes in the list, it's the name for the header that the items in the list are from. I see your point though, I suppose, that a select statement could be used in place of a query to base the list box on... but what is the benefit? Isn't the query just a visual representation of an SQL statement? I don't really know about these things.
Thanks for your clarification on the tab issue. I'll look for your remarks in regard to the above.
May God bless you with many Mangos!
Ken
bulsatar
You are correct (pretty much) that a query is just another form of sql statement. However, the difference between using a query and setting the list rowsource (or form/subform recordset) to a written out sql statement is huge! For instance, say we want to change 1 field on the fly from LastName to NickName (for whatever reason). To do this in code for a query you would have to write it like this:
hangeSQL = "SELECT NickName FROM MyTable"
CurrentDB.QueryDefs("MyQueryName").SQL = ChangeSQL
ListboxThatHasQuery.Requery
or
ListBoxThatHasSQLStatement.RowSource = "SELECT NickName FROM MyTable"
This also means that creating a multi-condition WHERE statement is easier also because then you can have your Select part of the statement saved as a variable (or not) and then just tack the Where statement at the end without worrying about filters and such. But, this will take a slight (depending on the speed of the computer and/or connection) performance hit as it re-pulls the information instead of just filtering out the condition. So that might look like this:
StartSQL = "SELECT NickName FROM MyTable "
WhereSQL = "1-1"
If Option1.Value = True Then WhereSQL = WhereSQL & " AND Date1 > #" & DateField.Value & "# AND Date1 < #" & EndDateField.Value & "#"
If Option2.Value = True Then WhereSQL = WhereSQL & " AND NickName = '" & NickNameField.Value & "'"
WhereSQL = Replace(WhereSQL, "1-1 AND","")
ListBox1.RowSource = StartSQL & WhereSQL
Hope that clarifies and doesn't confuse too much <
KenThompson
Yes, that makes sense. Thanks for the info!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.