Search Form Code

[Michael Adams]

This is what I have.'

Private Sub Bank()
'This sub is to search the table workordertracking for the criteria that is in the drop down box

'This is the variable for the form to be opened and populated
Dim BnkDocName As String
'This is the variable for the matching strings
Dim BnkLinkCriteria As String

'This is to assign the form "SubSearch" to the variable stdocName
'This will be the form which will be populated with the information in the form
BnkDocName = "SubSearch"

'me.combo5 is the combo box that is controled by the table "Bank" in the properties for the combo5 box
If Not IsNull(Me.Combo5) Then
    'This is how the literial strings are assigned to the variable which are matched from
    'the field in the table and the combo box on the search form
    BnkLinkCriteria = "[bank]=""" & Me!Combo5 & """"
End If
'This is the message box if the combo box is left empty
If IsNull(Me!Combo5) Then
    MsgBox ("Please select a Bank from the drop down...")
Else
    'This is the opening of the form and the criteria match
    
    'When the form is opened it will be populated with the information from
    'the controled source in each text box on the form
    DoCmd.OpenForm BnkDocName, , , BnkLinkCriteria
End If
End Sub

I call this in a click() event.

[f430]

here is my full code

Option Compare Database
Option Explicit
Dim cboOriginator As ComboBox


Private Sub cmdfindrecords_Click()
   Dim strFilter As String, strOldFilter As String
   

   strFilter = ""
    If Not IsNull(Me.productnum) Then _
       strFilter = strFilter & " AND " & _
          "([Part Number]=" & Me.productnum & ")"
   
   If Not IsNull(Me.Defect) Then _
       strFilter = strFilter & " AND " & "('" & Me.Defect & "' In([Defect Code 1]," & "[Defect Code 2]," & "[Defect Code 3]))"
  
   If Not IsNull(Me.associateid) Then _
       strFilter = strFilter & " AND " & "('" & Me.associateid & "' In([Associate]," & "[Associate 2]))"


  
  If Not IsNull(Me.date1) Then
       strFilter = strFilter & " AND ([CreationDate]"
  If Not IsNull(Me.date2) Then
       strFilter = strFilter & _
                " Between " & Format(Me.date1, "\#m/d/yyyy\#") & _
                " And " & Format(Me.date2, "\#m/d/yyyy\#") & ")"
  Else
       strFilter = strFilter & _
                "=" & Format(Me.date1, "\#m/d/yyyy\#") & ")"
  End If
  End If
  
  If strFilter > "" Then strFilter = Mid(strFilter, 6)
      Me.Filter = strFilter
      Me.FilterOn = (strFilter > "")


   
   
Debug.Print strFilter
End Sub


Private Sub date1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

Set cboOriginator = date1
calendar.Visible = True
calendar.SetFocus

If Not IsNull(cboOriginator) Then
   calendar.Value = cboOriginator.Value
Else
   calendar.Value = "DATE"
End If


End Sub


Private Sub date2_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

Set cboOriginator = date2
calendar.Visible = True
calendar.SetFocus

If Not IsNull(cboOriginator) Then
   calendar.Value = cboOriginator.Value
Else
   calendar.Value = "DATE"
End If

End Sub

Private Sub calendar_Click()

cboOriginator.Value = calendar.Value
cboOriginator.SetFocus
calendar.Visible = False
Set cboOriginator = Nothing

End Sub
Private Sub exit_Click()
On Error GoTo Err_exit_Click


    DoCmd.Close

Exit_exit_Click:
    Exit Sub

Err_exit_Click:
    MsgBox Err.Description
    Resume Exit_exit_Click
    
End Sub

[NeoPa]

Sorry Michael. I should have been clearer that I was addressing f430. I'm more than happy to help you on your quest if you would like me to, but not in this thread. This thread is owned by f430's question I'm afraid.

[Michael Adams]

No questions, My code works for comparing literal strings against the table. Before I used this code I was getting the same parameter thing f430 is getting. I believe it is looking at what the user is inputting and notices that there are different values in the field and is asking for wich one the user wants returned.

[NeoPa]

Sorry if there was any misunderstandin g Michael. There is no reason at all why you should not post anything you feel may be helpful (I'd be horrified if I thought, that you thought, that I was saying otherwise).

As it happens, I'm very grateful for your latest post as it brough f430's post to my attention that I'd missed when last I'd posted :)

@f430:
Nice. I'm leaving for home shortly, but that is more than I asked for and will do very nicely.

[Michael Adams]

I was not offended at all, I was just clarifying. I just hope that I could be of help cause I was running into the same problems that f430 has been running into.

[NeoPa]

f432: after i fixed the code, and try to search, it keeps asking me to enter parameter values for the following:
defect code 3
associate
associate 2

That indicates to me that none of those fields exist, or are actually called what you've called them. Check the names of the fields in whatever is the record source of your form.

f432: and i went into my code and created a breakpoint at the code provided in post #26 (the second one)

That would be post #20 line #30 I assume.

f432: and when i run the filter through it it gives me run-time error '3075' syntax error (missing operator) for both the associate and the defect code.

This is consistent with your first point about the three missing fields.

A breakpoint is a good idea, but in this case what would be more helpful (if the earlier instruction doesn't help you find the problem anyway) would be to move line #42 before line #36 (from your post #32), then post a copy of the data printed off when this line runs. I will look at it for you if we need to go that far.

[f430]

1) i went into my master table details and checked that everything had the same field name, i also checked the relationships between the tables (master table and products table, master and defects, master and associate), and everything was looking ok. then i went into my search form and checked that all the combo boxes were referring to the right field.
so im pretty sure everything was defined correctly.

2) you are correct, it is post#20 line #30, here is the code:

Me.Filter = strFilter

3) i moved the debugging code before the code above, and ran the filter.
by saying "copy the data printed off" i am assuming you mean the statement that comes up in the immediate window.
anyways, i ran the code and after i passed the debugging code, this message was displayed on my immediate window:

('1 - Rusty Retainers' In([Defect Code 1],[Defect Code 2],[Defect Code 3])) AND ('6' In([Associate],[Associate 2]))

where 1-Rusty Retainers is referring to one of the defects, and im guessing 6 is referring to one of the associate id for an associate number.

i tried to be as descriptive as possible, if you need more information please let me know.

Thank you for your help

[NeoPa]

f430: i tried to be as descriptive as possible, if you need more information please let me know.

You're certainly learning. This is much more pleasant to work with :)

The string is exactly what I was hoping to see. Not only is that precisely what I meant, but now I see it I'm confirmed on it being formatted perfectly.

If this is causing "Enter parameter Values" prompts still, then something somewhere is wrong with your database, and it's not the code.

With this in mind (and assuming that is still the case) it seems as if it might be time to attach a working (or in this case not working) copy of your database for me to look at. I'll include below some instructions I usually post to help you to do this correctly with the fewest possible problems.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[f430]

for some reason whenever i try to put my access file into the zipped folder, the search form does not show in the regular view. however if the search form is in the design view it has everything.
i followed all the steps, but thats what happens when i try to compress it.
any ideas??

[NeoPa]

f430:
for some reason whenever i try to put my access file into the zipped folder, the search form does not show in the regular view. however if the search form is in the design view it has everything.

I do not understand what you're talking about here. How does Zipping a file have any bearing whatsoever on what happens when you run it? How can you be running it while you are zipping it? This makes no sense to me at all.

Try to explain, step-by-step, what you are trying and what goes wrong at which point. The following questions may help.

Which points of my post have you completed successfully? Which point do you get stuck on? What do you notice is wrong at that point?

[f430]

i was able to complete all the steps correctly, except step #5 because when i went into my visual basic editor, that option was not available(i wasn't able to select it).
and what i mean about the form is, i checked the access file before i compress it, and everything seems to open and work correctly. however when i compress it in a zipped folder, i open the file and my search form is blank in regular view, but it has all the boxes in the design view.

[f430]

i think i figured out what is wrong with my database, and it definitely isn't my code.
the problem is that my search form is based on a search query. i went into my search query, and it didnt include defect code 3, associate, and associate 2. and that is why every time i searched, it asked me to enter parameter values. and i believe if i can get the correct sql into my search query, my code will work. here is what i have in my search query now, pls let me know how i can fix it.

SELECT [Master Table].ID, [Master Table].[Defect Code 1], [Master Table].CreationDate, [Master Table].[Defect Code 2], [Master Table].[Quantity Defective 2], [Master Table].[Part Number], [Master Table].[Defect Code 3], [Master Table].Associate, [Master Table].[Associate 2]
FROM [Master Table]
WHERE ((([Master Table].[Defect Code 1])=Forms!search!Defect1) And (([Master Table].CreationDate) Between Forms!Search!date1 And Forms!Search!date2) And (([Master Table].[Part Number])=Forms!Search![Product name])) Or ((([Master Table].[Defect Code 2])=Forms!Search!Defect1));

as you can see i have added the missing search criteria (defect code 3, associate and associate 2) and now when i search, i do not get any errors but my search form doesnt return any data.

Thanks

[NeoPa]

f430:
i was able to complete all the steps correctly, except step #5 because when i went into my visual basic editor, that option was not available(i wasn't able to select it).

Unless you're using 2007 (which would be pointless anyway after point #2) I cannot see why you might be having difficulty compiling the project. Was the option simply hidden due to not having been used recently. Office menus do that.

f430:
and what i mean about the form is, i checked the access file before i compress it, and everything seems to open and work correctly. however when i compress it in a zipped folder, i open the file and my search form is blank in regular view, but it has all the boxes in the design view.

I'm not sure why you would do that. No-one is likely to worry if it doesn't work from a zipped folder. As long as it works when unzipped, that is all that matters.

[NeoPa]

f430:
i think i figured out what is wrong with my database, and it definitely isn't my code.
the problem is that my search form is based on a search query. i went into my search query, and it didnt include defect code 3, associate, and associate 2. and that is why every time i searched, it asked me to enter parameter values. and i believe if i can get the correct sql into my search query, my code will work.

That is exactly right. Very much as I suspected, but I would have needed the database itself to say exactly where etc. It's a very good sign that you worked this out yourself. Well done.

f430:
here is what i have in my search query now, pls let me know how i can fix it.

as you can see i have added the missing search criteria (defect code 3, associate and associate 2) and now when i search, i do not get any errors but my search form doesnt return any data.

You seem to have changed direction with the SQL. I gave you detailed code on how to handle that dynamically, but now you want to use fixed code. That approach is much less flexible and also much more difficult to get right.

Is there a good reason why you have decided to start again on a different track? Essentially throwing away all the time and effort already expended on this.