Continuous form: Random blanking of fields (1 Viewer)

NBRJ

Registered User.
Local time
Today, 12:25
Joined
Feb 8, 2016
Messages
88
I hope some people will be so kind to help me with a form I'm having huge trouble with. I've attached a stripped back database (removed non-related tables and other forms and filled with test data that doesn't represent RL).

There are two forms here, both continuous:

  • fStaff
  • fLocation
Both are problematic, but once the problems with fLocation are identified, then I can fix fStaff as it's doing similar.

The form:
Header: filters records on the body of the form. There are some cascading cbo fields:

  1. cboFilterFIDFaculty
  2. cboFilterFIDSchool (depends on 1)
  3. cboFilterFIDSubject (depends on 2)
  4. cboFilterFIDSpecialism and cboFilterPointOfContact (both depends on 3)
2 is not enabled until a selection has been made in 1, same for 3 not enabled until 2 is selected. 4s are not enabled until 3 is selected. I haven't done this for the body, as I couldn't yet figure out how to have this to only effect the current record selected.

Body. Again, there are some cascading cbo fields:

  1. cboFIDFaculty
  2. cboFIDSchool (depends on 1)
  3. cboFIDSubject (depends on 2)
  4. cboFIDSpecialism and cboFilterPointOfContact (both depends on 3)
Note that there are also some txtfields, 1 for each cbo. These have GetFocus actions on them as the cbo's with the arrows take up too much space and I need to keep the record fields all on one line. Eventually these will be infront of the matching cbo's but for now they're messy and below the relevent cbos for testing purposes.

The problems. Where to start. Probably best that you download and see what's happening:

  1. If using the test data already in there, using any record's subject, see what happens to some of the txtfields AND cbos when a subject is changed. The same oddness happens when adding in a new record.They only appear back when reopen the form.
  2. If you click on any child cbo (ie. subject) in the body, it's not filtering the list based on it's parent as it should be doing (I have On Click event to recheck what selection the parent cbo is) and then if I click on a record that's not using the same subject/school it turns it blank (although the data is still there in the table) - I assume again, this is because it's not rechecking it's parent.
I've also had issues, and I can't reliably test it to break state with this, where the row source for various cbo's (both header and body) will have WHERE [field] = 4; or some other random record ID appear, when it should just be WHERE [field]; Why does the row source change?

I had requery in a lot of these subprocess after a filter is done, but it didn't have any effect. Clearly I'm missing some crucial concept here....
 

Attachments

  • Database-06042016 - Stripped - LocationsTest.accdb
    956 KB · Views: 66

NBRJ

Registered User.
Local time
Today, 12:25
Joined
Feb 8, 2016
Messages
88
Ok, I've removed the filter fields entirely for now, but it doesn't effect the problems on the body of the form.

For the Header filter I'm going to not bother with an after update event on them because it was getting so messy, but do it from a button that checks the contents of the filter fields in the header:
Code:
SELECT tLocation.*
FROM tLocation
WHERE (((tLocation.FIDBuilding) Like "*" & [Forms]![fLocation]![cboFilterFIDBuilding] & "*") 
AND ((tLocation.Room) Like "*" & [Forms]![fLocation]![txtFilterRoom] & "*") 
AND ((tLocation.FIDUsage) Like "*" & [Forms]![fLocation]![cboFilterFIDUsage] & "*") 
AND ((tLocation.FIDFaculty) Like "*" & [Forms]![fLocation]![cboFilterFIDFaculty] & "*") 
AND ((tLocation.FIDSchool) Like "*" & [Forms]![fLocation]![cboFilterFIDSchool] & "*") 
AND ((tLocation.FIDSubject) Like "*" & [Forms]![fLocation]![cboFilterFIDSubject] & "*") 
AND ((tLocation.FIDSpecialism) Like "*" & [Forms]![fLocation]![cboFilterFIDSpecialism] & "*") 
AND ((tLocation.PointOfContact) Like "*" & [Forms]![fLocation]![cboFilterPointOfContact] & "*") 
AND ((tLocation.AVCabinetPC) Like "*" & [Forms]![fLocation]![cboFilterAVCabinetPC] & "*") 
AND ((tLocation.AVWorksation) Like "*" & [Forms]![fLocation]![CheckAVFilter]) 
AND ((tLocation.FIDHistoricBuild) Like "*" & [Forms]![fLocation]![cboFilterFIDHistoricBuild] & "*"));
This is missing a line to account for the cboFilterWorkstationOperator (select from a list of: <; >; <=; >=; =; <>) and the txtFilterWorkstation (integer). I want to get a line that reads:

SELECT * FROM tLocation WHERE tLocation.Workstation

  • [Forms]![fLocation]![cboFilterWorkstationOperator] & [Forms]![fLocation]![txtFilterWorkstation])
  • [cboFilterWorkstationOperator] [txtFilterWorkstation]
  • >10 (or whatever operator they select and number they enter)
But that also needs to be option, as the rest is through LIKE... :banghead:
Maybe I need to insert a nested IF if I can't ge the Operator in from cboFilterWorkstationOperator?

EDIT: I think I've logically fixed this filter problem (which is a deviation from the original post problem anyway). I'll just have a if cboFilterWorkstationOperator is Null and txtFilterWorkstation isNull do a nested IF [and use the statement as above]. If not null do ..... modified statement....

Just curious is there any reason to use CASE over IF?

So I haven't sorted the original problem, I'll have just made the filter far tidier.
 
Last edited:

NBRJ

Registered User.
Local time
Today, 12:25
Joined
Feb 8, 2016
Messages
88
As mentioned above, I've changed the filter. Here's the code if anyone finds it useful.
Code:
Private Sub cmdFilter_Click()

Dim strFilterStart As String
Dim strFilterEnd As String

Dim strFilterBuilding As String
Dim strFilterRoom As String
Dim strFilterUsage As String
Dim strFitlerFaculty As String
Dim strFilterSchool As String
Dim strFilterSubject As String
Dim strFilterSpecialism As String
Dim strFilterPointOfContact As String
Dim strFilterAVCabinetPC As String
Dim strFilterAVWorkstation As String
Dim strFilterHistoricBuild As String
Dim strFilterOperatorAndWorkstation As String


strFilterStart = "SELECT * FROM tLocation WHERE ("
strFilterEnd = ");"

strFilterBuilding = "((tLocation.FIDBuilding) Like ""*"" & [Forms]![fLocation]![cboFilterFIDBuilding] & ""*"")"
strFilterRoom = "((tLocation.Room) Like ""*"" & [Forms]![fLocation]![txtFilterRoom] & ""*"")"
strFilterUsage = "((tLocation.FIDUsage) Like ""*"" & [Forms]![fLocation]![cboFilterFIDUsage] & ""*"")"
strFitlerFaculty = "((tLocation.FIDFaculty) Like ""*"" & [Forms]![fLocation]![cboFilterFIDFaculty] & ""*"")"
strFilterSchool = "((tLocation.FIDSchool) Like ""*"" & [Forms]![fLocation]![cboFilterFIDSchool] & ""*"")"
strFilterSubject = "((tLocation.FIDSubject) Like ""*"" & [Forms]![fLocation]![cboFilterFIDSubject] & ""*"")"
strFilterSpecialism = "((tLocation.FIDSpecialism) Like ""*"" & [Forms]![fLocation]![cboFilterFIDSpecialism] & ""*"")"
strFilterPointOfContact = "((tLocation.PointOfContact) Like ""*"" & [Forms]![fLocation]![cboFilterPointOfContact] & ""*"")"
strFilterAVCabinetPC = "((tLocation.AVCabinetPC) Like ""*"" & [Forms]![fLocation]![cboFilterAVCabinetPC] & ""*"")"
strFilterAVWorkstation = "((tLocation.AVWorksation) Like ""*"" & [Forms]![fLocation]![CheckAVFilter]) "
strFilterHistoricBuild = "((tLocation.FIDHistoricBuild) Like ""*"" & [Forms]![fLocation]![cboFilterFIDHistoricBuild] & ""*"")"
strFilterOperatorAndWorkstation = "((tLocation.Workstation) " & [Forms]![fLocation]![cboFilterWorkstationOperator] & [Forms]![fLocation]![txtFilterWorkstation] & ")"
' ((tLocation.Workstation)=21)

If IsNull(cboFilterWorkstationOperator) And IsNull(txtFilterWorkstation) Then

    Me.RecordSource = strFilterStart & strFilterBuilding & _
    " AND " & strFilterRoom & _
    " AND " & strFilterUsage & _
    " AND " & strFitlerFaculty & _
    " AND " & strFilterSchool & _
    " AND " & strFilterSubject & _
    " AND " & strFilterSpecialism & _
    " AND " & strFilterPointOfContact & _
    " AND " & strFilterAVCabinetPC & _
    " AND " & strFilterAVWorkstation & _
    " AND " & strFilterHistoricBuild & _
    strFilterEnd
 
 ElseIf Not IsNull(cboFilterWorkstationOperator) And Not IsNull(txtFilterWorkstation) Then
 
     Me.RecordSource = strFilterStart & strFilterBuilding & _
    " AND " & strFilterRoom & _
    " AND " & strFilterUsage & _
    " AND " & strFitlerFaculty & _
    " AND " & strFilterSchool & _
    " AND " & strFilterSubject & _
    " AND " & strFilterSpecialism & _
    " AND " & strFilterPointOfContact & _
    " AND " & strFilterAVCabinetPC & _
    " AND " & strFilterAVWorkstation & _
    " AND " & strFilterHistoricBuild & _
    " AND " & strFilterOperatorAndWorkstation & _
    strFilterEnd  'added workstation operator and number filter
 
 ElseIf IsNull(cboFilterWorkstationOperator) And Not IsNull(txtFilterWorkstation) Then
 
      Me.RecordSource = strFilterStart & strFilterBuilding & _
    " AND " & strFilterRoom & _
    " AND " & strFilterUsage & _
    " AND " & strFitlerFaculty & _
    " AND " & strFilterSchool & _
    " AND " & strFilterSubject & _
    " AND " & strFilterSpecialism & _
    " AND " & strFilterPointOfContact & _
    " AND " & strFilterAVCabinetPC & _
    " AND " & strFilterAVWorkstation & _
    " AND " & strFilterHistoricBuild & _
    " AND ((tLocation.Workstation)=" & [Forms]![fLocation]![txtFilterWorkstation] & _
    strFilterEnd  'If operator not selected but there is number in the txtFilterWorkstation search as if "=" had been selected.
    
ElseIf Not IsNull(cboFilterWorkstationOperator) And IsNull(txtFilterWorkstation) Then
    
    Me.RecordSource = strFilterStart & strFilterBuilding & _
    " AND " & strFilterRoom & _
    " AND " & strFilterUsage & _
    " AND " & strFitlerFaculty & _
    " AND " & strFilterSchool & _
    " AND " & strFilterSubject & _
    " AND " & strFilterSpecialism & _
    " AND " & strFilterPointOfContact & _
    " AND " & strFilterAVCabinetPC & _
    " AND " & strFilterAVWorkstation & _
    " AND " & strFilterHistoricBuild & _
    strFilterEnd 'same as if both cboFilterWorkstationOperator and txtFilterWorkstation are null (i.e. ignore)
    
End If

End Sub
I'm sure there's more concise ways to do it. But I've tested it and it's filtering great. And it's MUCH neater/shorter.

I still have the random blanking problem. I've just been chatting to someone and he thinks he's not sure there is a way to do what I want to do (have the record cbo boxes cascade AND recheck their parent coo when clicked into) and that's why I'm getting the random blanking. He thinks the best thing to do might be not to allow record edits on that form and a add new button to open a pop up form and add in records that way. Trouble is, that makes adding new records that bit more laborious.

Any suggestions? If I can get this form working, it's going to be the template for a few others I need to do....
 

NBRJ

Registered User.
Local time
Today, 12:25
Joined
Feb 8, 2016
Messages
88
I think I'm going to have to revisit how I do this form if no one has a solution. I found this suggestion below a week or so ago when I was looking if you can have a subform in a continuous form (no).
suggestion 1 said:
You can place a subform in the form footer (or form header) of a continuous form, and link it to the main form as usual. As the user scrolls through the records of the continuous form, the subform will display the corresponding records.
I did try to place a subform in the footer of my current fLocation, as per the quote above suggests but I get the following error: A form with a subform object can't have its DefaultView property set to Continuous Forms. So not a solution.
suggestion 2 said:
Another option is to place the continuous form and the "subforms" side by side in the detail section of an unbound main form. You'd have to link the "subforms" to a control on the continuous form, which can be a bit tricky, but it does work.
I'm liking this idea.

I'm thinking I could have two forms whose source is the same table, tLocation, on a unbound parent form. 1. Continuous. 2. Single. A record selected in the continuous form shows in the single form version for editing.

Does anyone have any idea what happens when you're on the new record on the continuous form (i.e at the bottom)? Will the single form display a new record view too? I hope so, because I'd probably be wanting to lock certain fields in the continuous (sub) form, so editing/adding is done in the single form.

Any idea how I'd go about linking the single form to the continuous form? I've seen examples of similar but the source tables have parent/child relationships and they're not using just one table as the source for both.

Also, if I wanted to put my filter in the header of the main (unbound) form these two will reside, how easy is it to redirect the filters to the continuous form?

And I'm assuming there isn't going to be a problem when this gets inserted into a navigation form, as long as my references are correct. If the filters are in the master, I'm only pointing the filter down to the subform.
 

Minty

AWF VIP
Local time
Today, 12:25
Joined
Jul 26, 2013
Messages
10,355
I did try to place a subform in the footer of my current fLocation, as per the quote above suggests but I get the following error: A form with a subform object can't have its DefaultView property set to Continuous Forms. So not a solution. I'm liking this idea.

This is actually incorrect - it won't let you set it at creation but you can edit it back to a continuous form afterwards.
 

NBRJ

Registered User.
Local time
Today, 12:25
Joined
Feb 8, 2016
Messages
88
Hi Minty, I thought I'd tried that and had it fail, but clearly not. I've just switched from a popup to putting it in the footer. Thanks for that.

Quick question, before I close the thread, related to this form, I've just put it into the Navigation Subform and the filter I have has broken.This filter works outside of the Navigation Subform (greatly reduced):
Code:
Private Sub cmdFilter_Click()

Dim strFilterStart As String
Dim strFilterEnd As String
Dim strFilterBuilding As String

strFilterStart = "SELECT * FROM qLocation WHERE ("
strFilterBuilding = "((qLocation.FIDBuilding) Like ""*"" & [Forms]![fLocation]![cboFilterFIDBuilding] & ""*"")"
strFilterEnd = ");"

Me.RecordSource = strFilterStart & strFilterBuilding & strFilterEnd

End Sub
Inside Navigation subform it breaks, despite this Debug working:
Code:
Dim testFilterFIDBuilding As String
testFilterFIDBuilding = [cboFilterFIDBuilding]
Debug.Print ; testFilterFIDBuilding
I've been looking at http://access.mvps.org/access/forms/frm0031.htm and still keep getting it wrong. I've even used a tip I read about using Expression builder to work out what it should be, which is apparently: [NavigationSubform].Form![cboFilterFIDBuilding] And that doesn't work either. Why do I, and so many others, have so much problem with this! :banghead:
 

NBRJ

Registered User.
Local time
Today, 12:25
Joined
Feb 8, 2016
Messages
88
Ok, sorted out the filter. Found this VERY useful comment elsewhere

Forms![Navigation Form].[NavigationSubform].Form.

The above GETS YOU to your current form you dropped on the nav form
So my filter for the form (when not in the navigation subform) was this:

[Forms]![fLocation]![cboFilterFIDBuilding]

Dropping it inside the Navigation Subform, it becomes:

Forms![fNavigation].[NavigationSubform].Form.[cboFilterFIDBuilding]

Why isn't fLocation mentioned? Oh well. This is an example of why people get confused about the navigation referencing. I was just about to build a tabbed main form and scrap the Navigation stuff MS provide entirely.

Right, one more problem with this form and it's pretty much done... will post in a bit if I can't find a solution! Else, I'll mark the thread as solved.
 

Minty

AWF VIP
Local time
Today, 12:25
Joined
Jul 26, 2013
Messages
10,355
Glad you got it sorted - I use quite a lot of continuous forms linked to others - great way of presenting drill down related data.

And you have highlighted one reason I think a lot of people don't use the inbuilt Navigation form, it's very awkward to work with. Most people tend to roll there own! :)
 

Users who are viewing this thread

Top Bottom