VBA Coding For "Search" and "Clear"

[cpepper5702]

Hey everyone,

Okay... I have been stuck on this issue for 2 days now and I am finally subjecting myself to outside help :banghead:. I am creating a large database that tracks computer assets. To help the system admins (SA's), I am trying to create a search form where they can filter by certain criterias. For example, if the user wants to search for servers running MS Server 2008 on the FISHPOND network, then the SA only has to type that information in the text boxes. After they are done with the results of the filter, I need them to clear the filter results. Everytime I click those buttons using my code, it gives me the error

"Run-time error '2580':

The record source 'SELECT * Asset Manager ' specified on this
form or report does not exist."

I'm probably not making a lot of senese on account that my brain is fried and I would rather give this job to our DBA team. I'd rather do it myself.

I can't post any screenshots from the original DB because it is on a different network that doesn't touch the internet. So I made another database that is similar to it to take screenshots of. I appologize again if I am being vague. If anyone needs more clarification please ask. Thanks a lot guys and gals! Below is the code for the "SEARCH" and "CLEAR" button.


Search Button Code: (It's telling me after RecordSource is the issue)

Private Sub Search_Click() 
On Error GoTo errr
    Me.Asset_Manager_subform.Form.RecordSource = "SELECT * FROM Asset Manager " & BuildFilter
    Me.Asset_Manager_subform.Requery
    Exit Sub
errr:
    MsgBox Err.Description
End Sub

Clear Button Code: (Like the last code, after RecordSource is the issue)

Private Sub Clear_Click()
    Me.Asset_Manager_subform.Form.RecordSource = "SELECT * FROM Asset Manager "
    Me.Asset_Manager_subform.Requery
    Hostname = ""
    Network = ""
    OSFamily = ""
    OSName = ""
    OSVersion = ""
    IPAddress = ""
    MACAddress = ""
    EquipmentClass = ""
    Manufacturer = ""
    Model = ""
    SerialNumber = ""
    ServiceTag = ""
    Hostname.SetFocus
 
End Sub

 

[pr2-eugin]

Hello cpepper5702, The problem I think is because, you are missing the BuildFilter Function.. Also, your reference should read..

Me[COLOR=Red][B]![/B][/COLOR]Asset_Manager_subform.Form.RecordSource = "SELECT [COLOR=Red][B]theFieldsByName[/B][/COLOR] FROM [Asset Manager] " & _
                          BuildFilter

It would be best if you used the appropriate Field Names as the SubForm needs its Controls to be specified.. and will look for the fields in the record source..

 

[missinglinq]

"SELECT * FROM Asset Manager " & BuildFilter

I see no problem with using the asterisk to pull all Fields from the Table, but Paul has pinpointed the problem, I think, without actually pointing it out! You have a Space in your Table name and have done nothing to let Access know this! As Paul did, in his code, you need Square Brackets around it, just as you would in referring to a Control or Field name, in VBA, that has Spaces in it:

"SELECT * FROM [B][COLOR="Red"][[/COLOR][/B]Asset Manager[B][COLOR="Red"]][/COLOR][/B] " & BuildFilter

Linq ;0)>

 

[cpepper5702]

Linq,

That makes sense what you are saying.

Paul,

Thank you for your advice. I got the clear button to work thankfully. But when I type a value into one of the boxes below and hit search, nothing happens. GOOD NEWS though, no runtime error . I used your advice but I did leave the asterisk to pull all of the fields I have in the subform. But I didnt quite understand what you mean't for the buildfilter. I didn't quite understand. Is that the exact way it should read in the line? It tells me that the underscore is an "Invalid character."

Me.Asset_Manager_subform.Form.RecordSource = "SELECT [COLOR=black]*[/COLOR] FROM [Asset Manager] " & [COLOR=red]_
                          BuildFilter[/COLOR]

When I type a value in the search fields below the subform, and hit search, nothing happens. I DID get the clear button to work based on what you guys said. So I'm very happy about that.

 

[cpepper5702]

I feel like I should say that I have limited VBA experiance. I'm still learning the ropes so I appologize if I am not connecting the dots as fast.

 

[ECEstudent]

Hi. I have a problem with my vba code and I would appreciate any help I can get!

What my code is supposed to be doing is taking in a user input (example: 38A1018X002) searching Table1 for it's associated value (ECX29-C7) Then taking in that found value and looking it up in a different table. I know how to do all of that no problem. My problem is that the value found is just PART of the new value that I would be looking for in a different table. For example: I would be looking up ECX29-C7 in a table field that contains values like:

ECX29-C7-D9-E6
ECX29-C8-U9
ECX30-C7-K5
ECX29-C7
.
.
.
[And so on]

What I need it to return in this example is ECX29-C7-D9-E6 AND ECX29-C7

I am just stuck on this one part. It needs to be in VBA. I can't do a query with this just because everything else I have is in code and this would be SO much simpler. Please help

 

[pr2-eugin]

When I type a value in the search fields below the subform, and hit search, nothing happens. I DID get the clear button to work based on what you guys said. So I'm very happy about that.

BuildFilter is a Function that would allow you to create the Filter or in other words Construct the WHERE Part.. If your SubForm is not filtered then I guess the function is not defined..

Okay do this.. Highlight BuildFilter in the code segment.. And Right Click on it, there should be a option, Definition if that come up with an Error then you do not have a Function defined..

 

[pr2-eugin]

Hello ECEstudent,

What my code is supposed to be doing is taking in a user input (example: 38A1018X002) searching Table1 for it's associated value (ECX29-C7) Then taking in that found value and looking it up in a different table.

Looks a bit Long winded don't you thin? Think there might be some Table design issue..

However to answer your Question, you can either use Left function or LIKE operator.. Using Left would be more appropriate I guess..

PS: Could you please start a New thread next time?

 

[ECEstudent]

Yes. I'm sorry. It was my first time here and I wasn't sure how this works. Thank you for your reply.

 

[pr2-eugin]

Yes. I'm sorry. It was my first time here and I wasn't sure how this works. Thank you for your reply.

That's alright.. Just for next time..

So did it sort your problem?