UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Error 3125: " Is Not A Valid Name..., Access 2016    
 
   
Nuke_It_Newport
post Jul 27 2018, 09:33 AM
Post#1



Posts: 28
Joined: 2-March 18
From: SW Michigan


Hi all-

I have a search form that doesn’t function properly. I hate asking for help without attempting to solve things myself. I have a lot of code from other people that I've learned A LOT from. I'm somewhat inexperienced with Access and SQL, and could use help. Here's what I have, and what I know.

Form: “frmTaskReportCenter”
Table: “tblTasks”
Query: “qselTRC”
Temporary query: “qselTemp”

The code creates a SQL string based on the entries from "frmTaskReportCenter", appends this string to the SQL string from "qselTRC", creates a temporary select query "qselTemp", runs this query, then deletes it. The filtered results are displayed on "frmTaskReportCenter".

Here’s the sequence of events:
• Alt – F11 and run module “UL” to show ribbon, etc.
• Navigate to form “frmTaskReportCenter”
• Select a location and / or a status from the combo boxes.
• Select “ANY” or “ALL” from the dropdown, then click “Apply”
• This action causes an Error 3215: “ is not a valid name…

Priority, Work Order, Date Created, and Due Date fields filter results correctly.
I’m not sure if this is an issue with the following syntax, or a query and / or table problem. I have tried everything I can think of, including modifying the query “qselTRC”, and changing the syntax of the following code:

CODE
' ********************************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
' ********************************************************************************
****
'
    'Text field example. Use quotes around the value in the string.
    
    If Len(Me.txtWorkOrder & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.txtWorkOrder) Then
        strWhere = strWhere & "WorkOrder = """ & Me.txtWorkOrder & """" & strOperator
    End If
'
'    'Another text field example. Use Like to find anywhere in the field.
'    If Not IsNull(Me.txtFilterMainName) Then
'        strWhere = strWhere & "([MainName] Like ""*" & Me.txtFilterMainName & "*"") AND "
'    End If
    
    'Number field example. Do not add the extra quotes.
    

    If Len(Me.cboPriority & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.cboPriority) Then
       strWhere = strWhere & "[Priority] = " & Me.cboPriority & strOperator
    End If
    
    
    If Len(Me.cboLocation & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.cboLocation) Then
       strWhere = strWhere & "[Location] = " & Me.cboLocation & strOperator
    End If
    
    If Len(Me.cboStatus & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.cboStatus) Then
        strWhere = strWhere & "[Status] = " & Me.cboStatus & strOperator
    End If
'
'    'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
'    If Me.cboFilterIsCorporate = -1 Then
'        strWhere = strWhere & "([IsCorporate] = True) AND "
'    ElseIf Me.cboFilterIsCorporate = 0 Then
'        strWhere = strWhere & "([IsCorporate] = False) AND "
'    End If
    
    'Date field example. Use the format string to add the # delimiters and get the right international format.
    
    If Len(Me.txtFromDateCreated & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtFromDateCreated) Then
        strWhere = strWhere & "([DateCreated]) >= " & Format(Me.txtFromDateCreated, conJetDate) & strOperator
    End If

    If Len(Me.txtToDateCreated & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtToDateCreated) Then
        strWhere = strWhere & "([DateCreated]) <= " & Format(Me.txtToDateCreated, conJetDate) & strOperator
    End If

    
    If Len(Me.txtFromDueDate & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtFromDueDate) Then
        strWhere = strWhere & "([DueDate]) >= " & Format(Me.txtFromDueDate, conJetDate) & strOperator
    End If

    If Len(Me.txtToDueDate & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtToDueDate) Then
        strWhere = strWhere & "([DueDate]) <= " & Format(Me.txtToDueDate, conJetDate) & strOperator
    End If


Here’s the complete code:

CODE
Private Sub cmdApplyFilter_Click()

Const cstrQueryName As String = "qselTemp"
Dim sWords As Variant
Dim strWhere As String
Dim TableToSearch As String
Dim strOperator As String
Dim intLenOperator As Integer
Dim SQL As String
Dim i As Integer
Dim j As Integer
Dim z As Integer
Dim b As Integer
Dim c As Integer
Dim strSQL_New As String
Dim strSQL_Org As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim qdNew As DAO.QueryDef
Dim intLenSQL As Integer
Dim strCombo As String
Dim strText As String
Dim lngLen As Long
Dim Pos As Long
Dim ValueCrnt As String
Dim ValueNew As String
Const conJetDate = "\#mm\/dd\/yyyy\#"
TableToSearch = "tblTasks"
   On Error GoTo errHandler
'
' ********************************************************************************
****
' Delete Temporary Query If It Exists
' ********************************************************************************
****
'
If CheckQuery("qselTemp") = "Yes" Then
    DoCmd.DeleteObject acQuery, "qselTemp"
End If
'
' ********************************************************************************
****
' Bring In The Original QueryDef (Instantiate It)
' ********************************************************************************
****
Set db = CurrentDb()
Set qdf = db.QueryDefs("qselTRC")
'
' ********************************************************************************
****
' Extract The SQL from the Original QueryDef
' ********************************************************************************
****
strSQL_Org = qdf.SQL
'
' ********************************************************************************
****
' Remove the Ending ";" and Space Characters from Original SQL
' ********************************************************************************
****
intLenSQL = Len(strSQL_Org) - 3
strSQL_Org = Left(strSQL_Org, intLenSQL)
'Debug.Print strSQL_Org
'
' ********************************************************************************
****
' Close The Original Query without any Modifications
' ********************************************************************************
****
qdf.Close
Set qdf = Nothing

' ********************************************************************************
***********
' Get value of operator ("ANY" or "ALL")
' ********************************************************************************
***********
'
If Not IsNull(Me.cboOperator) Then
    If (Me.cboOperator) = "ANY" Then
        strOperator = " AND "
    Else
        strOperator = " OR "
    End If
        intLenOperator = Len(strOperator)
Else
    MsgBox prompt:="Select an operator --  ""And"" or ""Or"".", buttons:=vbOKOnly, title:=gpgMBTitle & _
    "No Operator Selected"
    GoTo exitProc
End If
'
' ********************************************************************************
***********
    'Look at each search box, and build up the criteria string from the non-blank ones.
' ********************************************************************************
***********
'
    'Text field example. Use quotes around the value in the string.
    
    If Len(Me.txtWorkOrder & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.txtWorkOrder) Then
        strWhere = strWhere & "WorkOrder = """ & Me.txtWorkOrder & """" & strOperator
    End If
'
'    'Another text field example. Use Like to find anywhere in the field.
'    If Not IsNull(Me.txtFilterMainName) Then
'        strWhere = strWhere & "([MainName] Like ""*" & Me.txtFilterMainName & "*"") AND "
'    End If
    
    'Number field example. Do not add the extra quotes.
    

    If Len(Me.cboPriority & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.cboPriority) Then
        strWhere = strWhere & "[Priority] = " & Me.cboPriority & strOperator
    End If
    
    
    If Len(Me.cboLocation & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.cboLocation) Then
        strWhere = strWhere & "[Location] = " & Me.cboLocation & strOperator
    End If
    
    If Len(Me.cboStatus & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.cboStatus) Then
        strWhere = strWhere & "[Status] = " & Me.cboStatus & strOperator
        'strWhere = strWhere & "([status] Like ""*" & Me.cboStatus & "*"") AND "
    End If
'
'    'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
'    If Me.cboFilterIsCorporate = -1 Then
'        strWhere = strWhere & "([IsCorporate] = True) AND "
'    ElseIf Me.cboFilterIsCorporate = 0 Then
'        strWhere = strWhere & "([IsCorporate] = False) AND "
'    End If
    
    'Date field example. Use the format string to add the # delimiters and get the right international format.
    
    If Len(Me.txtFromDateCreated & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtFromDateCreated) Then
        strWhere = strWhere & "([DateCreated]) >= " & Format(Me.txtFromDateCreated, conJetDate) & strOperator
    End If

    If Len(Me.txtToDateCreated & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtToDateCreated) Then
        strWhere = strWhere & "([DateCreated]) <= " & Format(Me.txtToDateCreated, conJetDate) & strOperator
    End If

    
    If Len(Me.txtFromDueDate & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtFromDueDate) Then
        strWhere = strWhere & "([DueDate]) >= " & Format(Me.txtFromDueDate, conJetDate) & strOperator
    End If

    If Len(Me.txtToDueDate & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtToDueDate) Then
        strWhere = strWhere & "([DueDate]) <= " & Format(Me.txtToDueDate, conJetDate) & strOperator
    End If
'
' ********************************************************************************
****
' Get the text from the FreeText field
' ********************************************************************************
****
'
If Len(Me.txtFreeText & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If

If Not IsNull(Me.txtFreeText) Then
    Me.txtFreeText.Value = Trim(Me.txtFreeText.Value) & " "
    Me.txtFreeText.Value = Replace(Me.txtFreeText.Value, "  ", " ")
    sWords = Split(Me.txtFreeText.Value, ",")
    For z = LBound(sWords) To UBound(sWords)
        sWords(z) = Trim(sWords(z))  'remove any leading/trailing spaces
    Next z
    i = 9
    j = 10
    b = 1
    c = 2
  
     For z = LBound(sWords) To UBound(sWords) Step 1
      'Debug.Print "  " & z & "  " & sWords(z)
'
' ********************************************************************************
****
' Build the WHERE clause
' ********************************************************************************
****
'
     '**ALL Words/Fragments in any one field
'
If z = UBound(sWords) Then
             strWhere = strWhere & strOperator & " [" & CurrentDb.TableDefs("tblTasks").Fields(b).Name & "]  Like '*" & sWords(z) & "*'" & strOperator & " [" & CurrentDb.TableDefs("tblTasks").Fields(c).Name & "]  Like '*" & sWords(z) & "*'"
         Else
            strWhere = strWhere & strOperator & " [" & CurrentDb.TableDefs("tblTasks").Fields(b).Name & "]  Like '*" & sWords(z) & "*'" & strOperator & " [" & CurrentDb.TableDefs("tblTasks").Fields(c).Name & "]  Like '*" & sWords(z) & "*'" & strOperator
         End If
     Next z
    
'  If there are no matching records provide a message
If DCount("*", "tblTasks", strWhere) = 0 Then
    MsgBox "There are no records with those elements in the Title", vbOKOnly
    Exit Sub
End If
End If

'Debug.Print strWhere

' ********************************************************************************
***********
' If string "strWhere" has a trailing " AND ", then delete it.
' ********************************************************************************
***********
'
ValueCrnt = "And"
ValueNew = ""

Pos = InStrRev(strWhere, ValueCrnt)

    If Pos > 0 Then
        strWhere = Mid(strWhere, 1, Pos - 1) & Replace(strWhere, ValueCrnt, ValueNew, Pos)
    Else
'
' ********************************************************************************
***********
' If string "strWhere" has a trailing " OR ", then delete it.
' ********************************************************************************
***********
'
ValueCrnt = "Or"
ValueNew = ""

Pos = InStrRev(strWhere, ValueCrnt)

    If Pos > 0 Then
        strWhere = Mid(strWhere, 1, Pos - 1) & Replace(strWhere, ValueCrnt, ValueNew, Pos)
    End If
    End If

'Debug.Print strWhere
strSQL_New = strSQL_Org & strWhere
Debug.Print strWhere
Debug.Print strSQL_New
'
' ********************************************************************************
****
' Create Temporary Query, Set RecordSource Of Form, Close & Delete Temporary Query
' ********************************************************************************
****
'
Set qdNew = db.CreateQueryDef(cstrQueryName, strSQL_New)
Set qdNew = Nothing
Set db = Nothing
DoCmd.OpenQuery (cstrQueryName)
Forms.frmTaskReportCenter.Form.RecordSource = cstrQueryName
DoCmd.Close acQuery, (cstrQueryName)
Debug.Print strSQL_New
DoCmd.DeleteObject acQuery, cstrQueryName
'
' ********************************************************************************
****

exitProc:

    On Error Resume Next
    Exit Sub

errHandler:
    Call ErrMsg(intErr:=Err, intErl:=Erl, strErrFrm:=Screen.ActiveForm.Caption, strCtl:="cmdApplyFilter_Click")
    Resume exitProc
    Resume

End Sub


I know there are other issues with the code, and probably a lot of things that I'm not aware of yet. Any help is much appreciated!

Chad
Attached File(s)
Attached File  Task_Journal_07.27.2018.zip ( 606.52K )Number of downloads: 10
 
Go to the top of the page
 
JonSmith
post Jul 27 2018, 09:43 AM
Post#2


UtterAccess VIP
Posts: 4,053
Joined: 19-October 10



Ok, this is not a complete answer but some feedback.

CODE
Set qdNew = db.CreateQueryDef(cstrQueryName, strSQL_New)
Set qdNew = Nothing


This can just be the following since you don't use the qdNew object
CODE
db.CreateQueryDef(cstrQueryName, strSQL_New)


Opening the query here isn't doing anything.
CODE
DoCmd.OpenQuery (cstrQueryName)
Forms.frmTaskReportCenter.Form.RecordSource = cstrQueryName
DoCmd.Close acQuery, (cstrQueryName)


You can skip making this temp query altogether and just do the following which sets the recordsource to the SQL you have generated.
CODE
Forms.frmTaskReportCenter.Form.RecordSource =strSQL_New



There is alot more to review in this code but its time for me to clock off on a Friday and turn my brain off.
Go to the top of the page
 
orange999
post Jul 27 2018, 10:24 AM
Post#3



Posts: 1,975
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


I'm not sure how much of the code you "found online" nor how much you wrote yourself but here is more feedback to go with what Jon posted.

I tried a few searches and got errors.
I'm attaching a screen capture.


Attached File(s)
Attached File  otherError.png ( 54.35K )Number of downloads: 11
 
Go to the top of the page
 
GroverParkGeorge
post Jul 27 2018, 10:37 AM
Post#4


UA Admin
Posts: 35,896
Joined: 20-June 02
From: Newcastle, WA


I think I recognize that form as coming from my demo on my website. Right?

I guess, if that's the case, I should review your version to see where the differences are, if any.
Go to the top of the page
 
GroverParkGeorge
post Jul 27 2018, 10:39 AM
Post#5


UA Admin
Posts: 35,896
Joined: 20-June 02
From: Newcastle, WA


Hm. I DO like your interface better than mine. wary.gif
Go to the top of the page
 
GroverParkGeorge
post Jul 27 2018, 10:49 AM
Post#6


UA Admin
Posts: 35,896
Joined: 20-June 02
From: Newcastle, WA


Apparently, you did replace some of my original code with some of your own. So, there's that complication.

Go to the top of the page
 
GroverParkGeorge
post Jul 27 2018, 10:53 AM
Post#7


UA Admin
Posts: 35,896
Joined: 20-June 02
From: Newcastle, WA


Here's one syntax problem.
Attached File  Error.jpg ( 71.6K )Number of downloads: 0
Go to the top of the page
 
JonSmith
post Jul 27 2018, 11:11 AM
Post#8


UtterAccess VIP
Posts: 4,053
Joined: 19-October 10



QUOTE
I think I recognize that form as coming from my demo on my website. Right?


Waaaaaait a minute, I see an external link....... where are those posting rules......? tongue.gif
Go to the top of the page
 
BruceM
post Jul 27 2018, 11:51 AM
Post#9


UtterAccess VIP
Posts: 7,971
Joined: 24-May 10
From: Downeast Maine


Please, compile code before posting a database. I commented out quite a few procedures just to get the compilation to succeed.

You delete the QueryDef qselTemp, then you create it anew, then in the next line of code you remove it (set it to Nothing), then you try to use it as the Record Source. As Jon suggested, don't bother with the query defs (I hope I am summarizing somewhat accurately). Just use the SQL you built as the Record Source.

Once these things were done there was a different error because of a type mismatch in a query expression. I had tested with Location, and it turns out that you were using a number as the criteria for a text field. There is a LocationID field in tblTasks, but it did not have a value in any record. There was a Location text field also, which did have data, but the Where expression you built did not use the text value. I would store the LocationID and link to the Location name as needed, and not have the text value in tblTasks.

I would not be surprised if there is a similar issue with other fields, but I did not explore it further. Debug.Print strSQL_New will give you a SQL string that you can paste into SQL view of a new query for further evaluation and testing.

The point of code such as the following eludes me:

If Len(Me.txtWorkOrder & vbNullString) Then...

If the Len turns out to be 0, the expression will resolve to:

If 0 Then...

which I suppose is the same as:

If False Then...

Any other value would be True (I think). But I prefer to be specific:

Len(Me.txtWorkOrder & vbNullString) > 0 Then...

I can't sort out the reason for strSQL_New being a Cartesian product (it includes tblControlPanel without any joins), but I will assume there is a reason I haven't discovered.

FWIW, when building a Where condition I tend to do it by adding " OR " or " AND " (usually " AND ") to the beginning of each condition, rather than after. Assuming " AND ", after adding all conditions I do:

strWhere = " WHERE " & Mid(strWhere,6)

Others have a different preference, but there it is in case you are interested.
Go to the top of the page
 
GroverParkGeorge
post Jul 27 2018, 12:01 PM
Post#10


UA Admin
Posts: 35,896
Joined: 20-June 02
From: Newcastle, WA


It's free. I don't track downloads; I don't even use cookies. It's a way to share sample code.

On the other hand, it is a violation of the guidelines, I guess.

THanks for pointing out the inconsistency.
Go to the top of the page
 
BruceM
post Jul 27 2018, 12:58 PM
Post#11


UtterAccess VIP
Posts: 7,971
Joined: 24-May 10
From: Downeast Maine


George, I didn't know what was yours and what was the OP's. Now that I have looked at yours, I like the idea of assembling criteria into an array, then parsing the array to add the AND or OR. I'll give that a try the next time I need to build one of those strings, I think. As for leading or trailing AND or OR, just a preference of mine to use the leading operator and use MID to snip it from the string.

I also like the idea of grabbing the SQL from a query with no parameters, then adding the WHERE, rather than writing the entire SELECT along with joins in the VBA procedure. It's obvious now that I see it. I don't know how it eluded me all these years, but definitely using that in the future.

In any case, you assigned the SQL string directly as the RecordSource, rather than building a temporary QueryDef, which saves a number of steps to apply the exact same SQL. All of us suggested the same thing.

The issue with the Len statements that I described seems to be a place where the OP veered off course a little. Actually, it may be working as the OP constructed it, where it evaluated to If 0 Then... or If False Then..., but if so it's too subtle for my taste smile.gif

To the OP, I don't think you are that far off course. Compile the code, use the Debug.Print to test the SQL strings, and I think you'll have it. George's code provides some functions to help assemble the Where conditions, which may be helpful.
Go to the top of the page
 
Nuke_It_Newport
post Jul 27 2018, 03:50 PM
Post#12



Posts: 28
Joined: 2-March 18
From: SW Michigan


Grover Park George, You are correct sir! I learned a TON by attempting to "reverse engineer" your code. I have nowhere near the skill level necessary to write code like that! I'm still figuring it all out. Thank you for making your work available for free. I hope to "pay it forward" someday. hat_tip.gif
This post has been edited by Nuke_It_Newport: Jul 27 2018, 03:51 PM
Go to the top of the page
 
GroverParkGeorge
post Jul 27 2018, 04:20 PM
Post#13


UA Admin
Posts: 35,896
Joined: 20-June 02
From: Newcastle, WA


You're more than welcome.

COntinued success with the project.
Go to the top of the page
 
Nuke_It_Newport
post Jul 27 2018, 09:07 PM
Post#14



Posts: 28
Joined: 2-March 18
From: SW Michigan


BruceM, Sorry about that. I didn't compile because I wasn't concerned about the other code issues at this time. I've had tunnel vision trying to get the search form to function properly. It took me 3 months(!) to get the FreeText field to work correctly, and that was using George's code as a starting point! iconfused.gif I'm learning as I go.
Thanks for your advice. I changed where I insert the "strOperator" ("AND" or "OR") to the beginning of each condition in my WHERE statement as you suggested. I got rid of the "qselTemp", and the associated code. I am now setting the record source directly from the SQL string rather than using a temporary query, as suggested. The LEN statement was something I cut and pasted from the internet, but didn't look at closely. It's function was to insert the "WHERE" in the string if it wasn't there. It now seems to be a complicated (and unnecessary) way of doing things. I like your suggestion to build the string much better. I have been using Debug.Print extensively as I work to build a SQL string that works. The strSQL_New SELECT statement uses tblControlPanel because I have conditional formatting on the frmTaskReportCenter that is calculated based on values set in the control panel. Basically, it changes the color of the cell within a number of days from the due date based on a parameter set in the control panel. I'm not sure if I'm making any sense, or if there is an easier way to do this, but it works. confused.gif
All of these suggestions have helped me clean up my code quite a bit.
I'm afraid I'm a bit lost on how to fix my posted problem. I knew there was a "Type Mismatch" issue, but I don't quite understand the solution. For example, when I create a new record, I select a location from the combo box on form "frmNewTask", which is stored in "tblTasks". When I select a location to filter from the combo box on form "frmTaskReportCenter", the combo box outputs the LocationID number to the SQL statement's WHERE clause. How do I store the LocationID in "tblTasks" when the user creates a new record? Do I need to change the Row Source of the combo boxes on "frmNewTask" where the record is created? I'm not connecting the dots to make it work correctly. Thanks!
Go to the top of the page
 
BruceM
post Jul 30 2018, 10:36 AM
Post#15


UtterAccess VIP
Posts: 7,971
Joined: 24-May 10
From: Downeast Maine


The trouble with the Type Mismatch is that you were looking up the primary key value (LocationID) for the selected Location, but in the SQL you constructed you were applying that value to the Location field, which is text. The error was simply that you were trying to use a number value as the criterium for a text field. Criteria for text fields need to be delimited by quotes. Number fields to do not use quotes.

I don't have time now to review the SQL you constructed, but the general idea is to use the LocationID field rather than the Location field in the qselTRC query used for data entry. Change the control from a text box to a combo box, just as you did for the Location lookup combo box (the unbound one at the top of the form), with the first column hidden. When you view the form record you will see the Location, but behind the scenes Access is storing the LocationID value. For a report, create a query that links to tblLocationlkp, to show the actual location name on the report.

To summarize, I would store the LocationID value in tblTasks (you seem to have a field for it already) rather than the Location (text) value. This will also make it easier for users, who can select a location from a list rather than having to type it. If you need users to have the ability to add locations, you can do that, but I won't get into that just now. When you construct the SQL in VBA, just use the LocationID field rather than the Location field.

You can use the same approach for any value that is looked up from another table.

As for paying it forward, that day will surely come if you continue with Access. I started like you did, greatly aided by George and many others whose knowledge was matched by their graciousness. Then one day I realized I knew the answer to a question, so I answered it. I still ask questions, and still receive high level assistance from the many members whose knowledge continues to exceed mine, but now I can often provide assistance to others who are where I was years ago.

Best of luck with the project. In a day or two I may be able to put together a little demo of the concept above, if needed, but you're already very close.
Go to the top of the page
 
GroverParkGeorge
post Jul 31 2018, 09:00 AM
Post#16


UA Admin
Posts: 35,896
Joined: 20-June 02
From: Newcastle, WA


Thanks for pointing out the rules violation, Jon.
Go to the top of the page
 
JonSmith
post Jul 31 2018, 09:16 AM
Post#17


UtterAccess VIP
Posts: 4,053
Joined: 19-October 10



Sorry it went to a 'Report' George, Gord told me to do it.
Go to the top of the page
 
Nuke_It_Newport
post Aug 5 2018, 11:44 AM
Post#18



Posts: 28
Joined: 2-March 18
From: SW Michigan


Thanks for the help everyone. I finally got most of the bugs worked out of my search form. I still need to do some debugging. I can search with any combination of the fields (excluding the "Free Text" field), using "AND" or "OR" as an operator, and it filters correctly. If I type something in the "Free Text" Field of the form, whether I select other search criteria or not, using the "AND" operator, I get no results. If I do this using the "OR" operator, everything filters correctly. I think the problem may be in my code, where I'm building the SQL string. iconfused.gif I've been using Debug.Print to investigate, and the WHERE clause in the SQL string looks correct, but I've got to be missing something. I may post this as a new question, with more details, unless this is is the correct place to ask.
I'm still learning how queries work. I have been using Excel and VBA for years, so a lot of these concepts are new and strange to me. I have a bad habit of wanting to run before I crawl, I try to develop a working product, and then learn all the nuances of the code later. I did get some good books on Access, plus I'm searching and reading this forum every chance I get.
uarulez2.gif thanks.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 03:53 AM