Linking Append Query To A Command Button

Well, I'm all for doing it the right way the first time, so this thing doesn't go crazy on me for some reason down the road.

I'm assuming your solution is somewhat similar to this from your website? [I tried doing this and screwed up the input form as I couldn't figure out which table to pull the information for the query.]

I may have jumped a little too far ahead of myself before I really understood the basics of table relationships/primary keys/queries/etc.

Would I just use number keys for things in a dropdown list or for every field?

Would you mind giving me an example of what the query should look like or an example database that stores the keys and displays the text?
 
boblarson said:
I don't know if I can do that at this point though because it will take some time and I do need to get some of my own work done. :)
Click to expand...

Not a problem. Have to keep the priorities in order. :)

Perhaps you can help me with another problem I'm having with the other form (that may be a bit less time consuming).

On the "Search Database" section of my menu, I found this sample database that does exactly what I was looking to do. I tried to adapt it to my data, but I couldn't get it to work. When I enter a field to search, it always returns "Run-time error '2001': You canceled the previous operation." and the debugger directs me to this line:

Code: 
Me.sbfrmResults.Form.RecordSource = "SELECT * FROM qryEquipment " & BuildFilter

I'm assuming something is wrong with my query, but I'm really not sure what it is.
 
Do you have a function named BuildFilter? If so, what does it look like and how are you calling it? Or is BuildFilter a string variable in this procedure?

You can try using Debug.Print to see if BuildFilter is returning an appropriate value.

Debug.Print BuildFilter

(that is, if BuildFilter is a variable and not a procedure)
 
Here's the VBA code for the BuildFilter Function.

Anything look out of place there?

Code: 
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varProblems As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    varWhere = Null  ' Main filter
    varProblems = Null  ' Subfilter used for Problems
 
    ' Check for MNumber
    If Me.txtMNumber > "" Then
        varWhere = varWhere & "[M Number] LIKE """ & Me.txtMNumber & "*"" AND "
    End If
 
    ' Check for ENumber
    If Me.txtENumber > "" Then
        varWhere = varWhere & "[E Number] LIKE """ & Me.txtENumber & "*"" AND "
    End If
 
    ' Check for Work Order Number
    If Me.txtWorkOrder > "" Then
        varWhere = varWhere & "[WO Number] LIKE """ & Me.txtWorkOrder & "*"" AND "
    End If
 
    ' Check for Months
    If Me.cmbMonths > 0 Then
        varWhere = varWhere & "[Month Complete] = " & Me.cmbMonths & " AND "
    End If
 
    ' Check for Years
    If Me.cmbYears > 0 Then
        varWhere = varWhere & "[Year Complete] = " & Me.cmbYears & " AND "
    End If
 
     ' Check for Area
    If Me.cmbArea > 0 Then
        varWhere = varWhere & "[Area] = " & Me.cmbArea & " AND "
    End If
 
    ' Check for Problems in multiselect list
    For Each varItem In Me.lstProblems.ItemsSelected
        varProblems = varProblems & "[Further Problems] = """ & _
                    Me.lstProblems.ItemData(varItem) & """ OR "
 
    Next
 
    ' Test to see if we have subfilter for problems...
    If IsNull(varProblems) Then
        ' do nothing
    Else
        ' strip off last "OR" in the filter
        If Right(varProblems, 4) = " OR " Then
            varProblems = Left(varProblems, Len(varProblems) - 4)
        End If
 
        ' Add some parentheses around the subfilter
        varWhere = varWhere & "( " & varProblems & " )"
    End If
 
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
 
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
 
    BuildFilter = varWhere
 
End Function
 
This is my modified version (parts I changed in red):
Code: 
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varProblems As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    varWhere = Null  ' Main filter
    varProblems = Null  ' Subfilter used for Problems
 
    ' Check for MNumber
    If [COLOR=red]Len([/COLOR]Me.txtMNumber [COLOR=red]& vbNullString)[/COLOR] > 0 Then
        varWhere = varWhere & "[M Number] LIKE """ & Me.txtMNumber & "*"" AND "
    End If
 
    ' Check for ENumber
    If [COLOR=red]Len([/COLOR]Me.txtENumber [COLOR=red]& vbNullString)[/COLOR] > 0 Then
        varWhere = varWhere & "[E Number] LIKE """ & Me.txtENumber & "*"" AND "
    End If
 
    ' Check for Work Order Number
    If [COLOR=red]Len([/COLOR]Me.txtWorkOrder [COLOR=red]& vbNullString)[/COLOR] > 0 Then
        varWhere = varWhere & "[WO Number] LIKE """ & Me.txtWorkOrder & "*"" AND "
    End If
 
    ' Check for Months
    If [COLOR=red]Len(Me.cmbMonths & vbNullString) > 0 And [/COLOR]Me.cmbMonths > 0 Then
        varWhere = varWhere & "[Month Complete] = " & Me.cmbMonths & " AND "
    End If
 
    ' Check for Years
    If [COLOR=red]Len(Me.cmbYears & vbNullString) > 0 And [/COLOR]Me.cmbYears > 0 Then
        varWhere = varWhere & "[Year Complete] = " & Me.cmbYears & " AND "
    End If
 
     ' Check for Area
    If [COLOR=red]Len(Me.cmbArea & vbNullString) > 0 And [/COLOR]Me.cmbArea > 0 Then
        varWhere = varWhere & "[Area] = " & Me.cmbArea & " AND "
    End If
 
    ' Check for Problems in multiselect list
    For Each varItem In Me.lstProblems.ItemsSelected
        varProblems = varProblems [COLOR=red]& [/COLOR]Me.lstProblems.ItemData(varItem) [COLOR=red]& ","
[/COLOR] 
    Next
 
    ' Test to see if we have subfilter for colors...
    If [COLOR=red]Not[/COLOR] IsNull(varProblems) Then
        ' strip off last "OR" in the filter
        If Right(varProblems, [COLOR=red]1[/COLOR]) = [COLOR=red]","[/COLOR] Then
            varProblems = Left(varProblems, Len(varProblems) - [COLOR=red]1[/COLOR])
        End If
 
        ' Add some parentheses and the word In around the subfilter
        varWhere = varWhere & [COLOR=red]" [Further Problems] In [/COLOR]( " & varProblems & " )"
    End If
 
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = [COLOR=red]vbNullString[/COLOR]
    Else
        varWhere = "WHERE " & varWhere
 
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
 
    BuildFilter = varWhere
 
End Function
 
Hmm...

Love how the debugger doesn't take you to the source of the problem :rolleyes:


Yllm9.jpg


Pretty sure I inserted everything you had there

Code: 
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varProblems As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    varWhere = Null  ' Main filter
    varProblems = Null  ' Subfilter used for Problems
    
    ' Check for MNumber
    If Len(Me.txtMNumber & vbNullString) > 0 Then
        varWhere = varWhere & "[M Number] LIKE """ & Me.txtMNumber & "*"" AND "
    End If
    
    ' Check for ENumber
    If Len(Me.txtENumber & vbNullString) > 0 Then
        varWhere = varWhere & "[E Number] LIKE """ & Me.txtENumber & "*"" AND "
    End If
    
    ' Check for Work Order Number
    If Len(Me.txtWorkOrder & vbNullString) > 0 Then
        varWhere = varWhere & "[WO Number] LIKE """ & Me.txtWorkOrder & "*"" AND "
    End If
    
    ' Check for Months
    If Len(Me.cmbMonths & vbNullString) > 0 And Me.cmbMonths > 0 Then
        varWhere = varWhere & "[Month Complete] = " & Me.cmbMonths & " AND "
    End If
    
    ' Check for Years
    If Len(Me.cmbYears & vbNullString) > 0 And Me.cmbYears > 0 Then
        varWhere = varWhere & "[Year Complete] = " & Me.cmbYears & " AND "
    End If
    
     ' Check for Area
    If Len(Me.cmbArea & vbNullString) > 0 And Me.cmbArea > 0 Then
        varWhere = varWhere & "[Area] = " & Me.cmbArea & " AND "
    End If
    
    ' Check for Problems in multiselect list
    For Each varItem In Me.lstProblems.ItemsSelected
        varProblems = varProblems & Me.lstProblems.ItemData(varItem) & ","
        
    Next
    
    ' Test to see if we have subfilter for Problems...
    If Not IsNull(varProblems) Then
        ' do nothing
    Else
        ' strip off last "OR" in the filter
        If Right(varProblems, 1) = "," Then
            varProblems = Left(varProblems, Len(varProblems) - 1)
        End If
        
        ' Add some parentheses around the subfilter
        varWhere = varWhere & " [Further Problems] In ( " & varProblems & " )"
    End If
    
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = vbNullString
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
    
End Function
 
You missed this:
Code: 
   If Not IsNull(varProblems) Then
        ' do nothing
    Else

That should have the 'do nothing and Else removed.

It should look like this:

Code: 
    ' Test to see if we have subfilter for colors...
[COLOR=red]    If Not IsNull(varProblems) Then
        ' strip off last comma in the filter
        If Right(varProblems, 1) = "," Then
[/COLOR]            varProblems = Left(varProblems, Len(varProblems) - [COLOR=black]1[/COLOR])
        End If
 
        ' Add some parentheses and the word In around the subfilter
        varWhere = varWhere & [COLOR=red][COLOR=black]" [Further Problems] In[/COLOR] [/COLOR]( " & varProblems & " )"
    End If
 
boblarson said:
You missed this:
Code: 
   If Not IsNull(varProblems) Then
        ' do nothing
    Else

That should have the 'do nothing and Else removed.

It should look like this:

Code: 
    ' Test to see if we have subfilter for colors...
[COLOR=red]   If Not IsNull(varProblems) Then[/COLOR]
[COLOR=red]       ' strip off last comma in the filter[/COLOR]
[COLOR=red]       If Right(varProblems, 1) = "," Then[/COLOR]
            varProblems = Left(varProblems, Len(varProblems) - [COLOR=black]1[/COLOR])
        End If
 
        ' Add some parentheses and the word In around the subfilter
        varWhere = varWhere & [COLOR=red][COLOR=black]" [Further Problems] In[/COLOR] [/COLOR]( " & varProblems & " )"
    End If
Click to expand...

HA! Alright...that's what I'm talking about!

You, my friend, need a Paypal button, so I can donate my firstborn child.

Is there a way to lock the subform so it cannot be edited? The "Locked" property didn't quite do it.
 
Have you tried:

Me.YourSubformControlName.Form.AllowEdits = False

Where YourSubformControlName is the name of the control on the parent form which HOUSES the subform, not the subform name itself (unless they share the same exact name).
 
Where would I find the name of this control?

Also, would that go into the ON LOAD Event of the form or somewhere else?
 
Hmm...still a bit confused.

I used the Name of the container it's in (.sbfrmResults) in the On Load Event of the main form, but it doesn't lock the subform.

Code: 
 Me.sbfrmResults.Form.AllowEdits = False

Something I'm missing, perhaps?
 
You probably aren't missing anything. It is probably me. I now seem to remember (I haven't needed to do this in quite a while) that the subform allow edits follows through from the main form's allow edits. So if the main form is set to allow edits, then the subform will too.

So if you want to lock things for the subform you can use some code in the on Current event of the subform:
Code: 
Dim ctl As Control
   
For Each ctl in Me.Controls
   Select Case ctl.ControlType
       Case acTextBox, acComboBox, acListBox, acOptionGroup
           ctl.Locked = True
    End Select
Next
 
For some reason, I wasn't able to get that code to work, but I feel like a goober now.

All I had to do was select the Subform > Data Tab > Change Allow Edits, and Allow Deletions to "No".

I'm about <---> close to everything I need here. Just a couple more questions.

For some reason, on the "Record Form in Database" form, whenever I make an entry into any other field, the option group I have set up automatically fills in the "No" checkbox. How can I make it stop doing that?

Also, I've included a "Precision Sheet Link Field" on the "Record Form in Database" that opens up a file explorer to link .pdf files for each record. It adds the link as it should. When I use the "Search Database" form, the links show up as #Name?.

Each subform on "Record Form" and "Search Form" pulls from the same table, so I'm a bit perplexed.
 

Attachments

Check the field the Option Group is set to (and the option group control itself) as in both places there is a DEFAULT value that can be set and perhaps it is set in one of those places.

As for the link problem - don't store it as a link, just as text. Also, make sure the text box that shows the link doesn't have the same name as the field it is bound to.
 
boblarson said:
Check the field the Option Group is set to (and the option group control itself) as in both places there is a DEFAULT value that can be set and perhaps it is set in one of those places.
Click to expand...

Both the Option Group and Option Group Control default values are blanked. They remain "grayed out" until data is entered into any of the other fields.

boblarson said:
As for the link problem - don't store it as a link, just as text. Also, make sure the text box that shows the link doesn't have the same name as the field it is bound to.
Click to expand...

I wanted to store it as a link to the actual file, so you could see the document that goes along with the record. Is that sort of thing not possible?

Thanks again for your help thus far. :D
 
MCatz said:
Both the Option Group and Option Group Control default values are blanked. They remain "grayed out" until data is entered into any of the other fields.
Click to expand...
Not sure then unless there is some code somewhere setting it.

I wanted to store it as a link to the actual file, so you could see the document that goes along with the record. Is that sort of thing not possible?
Click to expand...
Storing the path and file name is fine. Don't make it a hyperlink. You can make it do the same thing as such by putting

FollowHyperlink Me.YourTextBox

in the text box's On Click event and formatting it in blue and underlining. You can then, in the control's properties set the IsHyperlink to YES so that the hand shows up when the mouse is above it.
 
Stilll shows up as #Name? even after I converted the field to a Memo (to fit the amount of text). I even went down the column and just filled in words, but it looked the same on the "Search Form" subform.
 
You must log in or register to reply here.

Similar threads

C
Can the "Close" (x) button event on a form be altered with VBA code?
Replies
11
Views
300
June7
June7
brucemc777
Show/Hide a control based on whether or not 4 other controls have contents
Replies
4
Views
85
brucemc777
brucemc777
E
I need to be able to type in text box and for the list box to show me all similar text. No VBA )-:
Replies
19
Views
918
Pat Hartman
P
G
How to change the format of a command button
Replies
1
Views
319
Gasman
Gasman
C
Solved Highlight backcolor in only 1 input on a continuous form
Replies
10
Views
846
riktek
riktek

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom