Create search form in Access

[Slaxer13]

That code was the base of mine, of course i had it modified because copying full codes its not the best way to go and modifying them is always a good way to learn a few more things for us new to access and vba ;)
Cumps, Slaxer13

[twinnyfo]

ash,

All code is plagiarized to some more or lesser degree. But, slaxer is correct. Review the code posted, look at how it would be incorporated into your code and proceed from there.

In my experience, the MS website provides the absolute minimal basics for their VBA. However, they never (EVER) provide any information on how to actually USE anything. It is very frustrating. On the flip side, this forum has actual users and programmers, some of whom have been doing this for a very long time (I've been involved in DBs in some way for close to 20 years, now). We've done a lot and have, more likely than not, come across your same problem at least once. At the same time, I learn new stuff on this site every day!

[ashjones86]

Good Morning all,

So i have been looking into the code Slaxer13 sent me, and the following line of code.

Private Sub btnSearch_Click()
    
    ' Update the record source
    Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " & BuildFilter
    
    ' Requery the subform
    Me.frmsubClients.Requery
End Sub

Would the line

Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " & BuildFilter

be where the query is called from ? so for example if my query is called Vehicles Query would i change qryClientData to Vehicles Query ?

[aleciagibbons14]

thanks for these allow me also to try these shared idea

[Slaxer13]

Good morning ppl,

If i am not mistaken that line is where it selects all from the query. The following requery makes it only show the records that are filtered with your search criteria. (I have only worked really with access for a few weeks so forgive me for any mistake)

[ashjones86]

Its ok Slaxer, this is my 5th day with Access so i have no idea by comparison, so the table that link you posted shows in design view as a form, im trying to find a means of putting my current table there and change the search options to match that, i have found the search names in the code, so i can change those to match those in the table, but where to add the table instead of the one that is there is baffling me, this is kinda fun though....in a hair pulling kinda way :)

[Slaxer13]

If you want i can see where i change my code to fit my db and put the code here in bold so that you can see where to change.Oor if you prefer you can try it and if you can´t do it i'll post it.

[Slaxer13]

I have created a search button in my main form that opens a search form, in which i have two combo boxes and one text box. One of the combos has the criteria (in my case it has Name, NIF, Address and Phone Number), the other combo has only one field that i put there manually that says "Contains" and the text box is where you input the data to search. Right now i am trying to do the opposite. I am trying to make the "Doesn't Contain" wich will search all records except those that have that field. But it's still a "book not yet ready for release" xP

[Slaxer13]

Ash it seems we can only send three pm a day. My adress is {email redacted}. You can send now if you want.
Cheers, Slaxer13

[ashjones86]

Spot on cheers, will email now.

[NeoPa]

Regretfully, as so much effort went into the post and it's really very helpful, I've had to reset Twinny's post as Best Answer because it doesn't pertain directly to the question. That doesn't stop you appreciating how helpful it was of course.

As for the question - it seems that Slaxer's assistance was the most directly responsible for the solution, but as it was posted as an attachment rather than displayed in code, it cannot be set as Best Answer either. I'll leave this without one - particularly as much of the discussion has been off-topic. Valuable to those involved, but not very directly related to the question. Not a problem. Many threads are like that. Particularly helpful for beginners - but less valuable for those searching for answers later (except for other beginners I suppose).

Now back to the question for me!
I would simply point out that although searching can be implemented in Access, a more practical approach considering the tools available is to apply a filter. This can be done on-the-fly as well as when the form is opened. Basically it provides a very powerful and flexible way to find things via Forms and Reports. The opportunities are varied and various. Some of the potential, and how to achieve it, is discussed in Example Filtering on a Form.

[Slaxer13]

Sorry it went a little offtopic, it wasn't supposed to but we beginners have the problem of only having the possibility if sending 3 pm a day :/ which is why this happened. From my part sorry 'bout that. As for the answer to the question of the topic i think yours is the best, since mine is an attachment like you said.

[NeoPa]

I'm not sorry it went off-topic my friend :-) This OP has benefitted greatly from much that was off-topic in this thread. It may not be a great thread for searching through later but has great value nevertheless to the OP and possibly even yourself.

As for my answer - that wouldn't be a Best Answer without my stripping out most of the original explanation, and I'm not convinced the OP would even recognise the value of it unless they'd tried it and learned from it. In fact, it seems that Jim already made the same suggestion back in post #2 but the OP didn't recognise the value of it then. Understandable, as those new to databases often have to change the way they think to get into it properly.

All in all, your (Slaxer) additions to the thread have been valuable and you have nothing to apologise for :-)

[ashjones86]

Hello everyone, so with the help of Slaxer13 i have been plodding away, but still with no success, so this is the code im using now, kindly linked by Slaxer -

Private Sub Command19_Click()
If IsNull(Text17) = False Then
      Me.Recordset.FindFirst "[Vehicles]=" & Text17
      Me!Text17 = Null
      If Me.Recordset.NoMatch Then
         MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me!Text17 = Null
      End If
   End If
End Sub

However, the error im getting is this - Run-time error '3070':
The Microsoft office access database engine does not recognize 'Vehicles' as a valid name or expression.

Would anyone be able to help ?

[ashjones86]

Ok so its working .....kinda, but it doesn't find anything, so here is the code now -

Option Compare Database
Option Explicit

Private Sub Command19_Click()
If IsNull(Text17) = False Then
      Me.Recordset.FindFirst "Vehicle Model" = " & Text17"
      Me!Text17 = Null
      If Me.Recordset.NoMatch Then
         MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me!Text17 = Null
      End If
   End If
End Sub

So Vehicle Model is one of my fields in my table which is where the registrations are help, but when i type it in, and click search, it says sorry no record found.