Trying to create a Search Button....I'm Extremely New ToThis

[mphotographer]

Hello all....help

I'm as green as they come when it comes to using access and vb so any thoghts
would be beneficial I am sure. Here is my situation. I have never used
either before and I recently got thrown into a project where I need to create
a database of all the CAD drawings in the shop. So here I am trotting along
with my "Access 2000 Bible" trying to make a dent. To this point I have been
able to make my table (tABLE1) and a form (test 2). Found in my table is:

tABLE1
File (represents Drawing #)
Cust (Customer)
Name (Part Name)
Rev (Drawing Revision Number)
Date (Date Drawing was Created)
By (Drawings Creator)
Partno (Part # of Piece in Drawing)
CAD Image (Bound Image of the corresponding File)
Notes (Notes about Drawing or Part)

I have created a Form (test 2), in which each of these have been attached to
a text field. I am able to update my table as I add new info and images but
at this point, I need to create a form that incorporates a search for these
items rather than individually updating a querry for each new search. I am
hoping to create a text field attached to a command button. As I would type
in say "mill bushing" it would return all the items that have "mill bushing"
located somewhere in its Name. I was able to take some code from another
database that is on our network and try to tweek it to my own needs since I
really no nothing about creating code from scratch. I feel like i'm trying
to do algebra w/o knowing how to add and subtract.

Here is a copy of my code attached to a button I am trying to make currently.
When I run the button, I get:
run-time error '3075'
Syntax error in string in query expression 'Name=ball'"
(As a note, ball is what i have typed in my search field)

Private Sub NameSearch_Cmd_Click()

'Enable error handling

Dim Name As String 'Name of record sought

'Check if part name to be found has been entered in NameSearch_Ref text
box
If IsNull(Me![NameSearch_Ref]) Then

'No part name entered: display error message
MsgBox "Please enter part name.", vbOKOnly + vbExclamation, "Missing
Part Name"

Else

'Part Name is available: Find part name, if any, with part name in
NameSearch_Ref text box
LookUpResult = DLookup("Name", "tABLE1", "Name=" & Me.NameSearch_Ref
& "'")

'Check result of DLookup function
If IsNull(LookUpResult) Then

'No record found: display error message
MsgBox "No part " & Me![NameSearch_Ref] & " found.", vbOKOnly +
vbExclamation, _
"Invalid Part Name"

Else

'Record found: Display record and go to NameSearch_Ref text box
DoCmd.ShowAllRecords
DoCmd.GoToControl Me!Name.Name
DoCmd.FindRecord LookUpResult

End If

'Erase part name entered
Me![NameSearch_Ref] = Null

End If

'Go to NameSearch_Ref text box
DoCmd.GoToControl Me![NameSearch_Ref].Name

'Exit procedure
Exit_Find_NameSearch_Cmd_Click:
Exit Sub

'Display error message and exit
Err_Find_NameSearch_Cmd_Click:
MsgBox Err.Description
Resume Exit_Find_NameSearch_Cmd_Click

End Sub 'Find_NameSearch_Cmd_Click




I tried to include as much info as i could think would be appropriate. Any
help would be so much appreciated. thank you

 

[Guest]

A couple of questions:
You say you want to return all records with a specific value in the part
number name. Where do you want them to be?
Is your form a single record form, a continuous form, or is it a sub form in
datasheet view?
--
Dave Hargis, Microsoft Access MVP

Hello all....help

I'm as green as they come when it comes to using access and vb so any thoghts
would be beneficial I am sure. Here is my situation. I have never used
either before and I recently got thrown into a project where I need to create
a database of all the CAD drawings in the shop. So here I am trotting along
with my "Access 2000 Bible" trying to make a dent. To this point I have been
able to make my table (tABLE1) and a form (test 2). Found in my table is:

tABLE1
File (represents Drawing #)
Cust (Customer)
Name (Part Name)
Rev (Drawing Revision Number)
Date (Date Drawing was Created)
By (Drawings Creator)
Partno (Part # of Piece in Drawing)
CAD Image (Bound Image of the corresponding File)
Notes (Notes about Drawing or Part)

I have created a Form (test 2), in which each of these have been attached to
a text field. I am able to update my table as I add new info and images but
at this point, I need to create a form that incorporates a search for these
items rather than individually updating a querry for each new search. I am
hoping to create a text field attached to a command button. As I would type
in say "mill bushing" it would return all the items that have "mill bushing"
located somewhere in its Name. I was able to take some code from another
database that is on our network and try to tweek it to my own needs since I
really no nothing about creating code from scratch. I feel like i'm trying
to do algebra w/o knowing how to add and subtract.

Here is a copy of my code attached to a button I am trying to make currently.
When I run the button, I get:
run-time error '3075'
Syntax error in string in query expression 'Name=ball'"
(As a note, ball is what i have typed in my search field)

Private Sub NameSearch_Cmd_Click()

'Enable error handling

Dim Name As String 'Name of record sought

'Check if part name to be found has been entered in NameSearch_Ref text
box
If IsNull(Me![NameSearch_Ref]) Then

'No part name entered: display error message
MsgBox "Please enter part name.", vbOKOnly + vbExclamation, "Missing
Part Name"

Else

'Part Name is available: Find part name, if any, with part name in
NameSearch_Ref text box
LookUpResult = DLookup("Name", "tABLE1", "Name=" & Me.NameSearch_Ref
& "'")

'Check result of DLookup function
If IsNull(LookUpResult) Then

'No record found: display error message
MsgBox "No part " & Me![NameSearch_Ref] & " found.", vbOKOnly +
vbExclamation, _
"Invalid Part Name"

Else

'Record found: Display record and go to NameSearch_Ref text box
DoCmd.ShowAllRecords
DoCmd.GoToControl Me!Name.Name
DoCmd.FindRecord LookUpResult

End If

'Erase part name entered
Me![NameSearch_Ref] = Null

End If

'Go to NameSearch_Ref text box
DoCmd.GoToControl Me![NameSearch_Ref].Name

'Exit procedure
Exit_Find_NameSearch_Cmd_Click:
Exit Sub

'Display error message and exit
Err_Find_NameSearch_Cmd_Click:
MsgBox Err.Description
Resume Exit_Find_NameSearch_Cmd_Click

End Sub 'Find_NameSearch_Cmd_Click




I tried to include as much info as i could think would be appropriate. Any
help would be so much appreciated. thank you

 

[mphotographer]

Ok...a quick look at my form is below... I would like the result of my search
to fill in the information in the form and be in a record 1 of xxx format.

Customer _______________________________________

File _______________________________ Rev _______

Name __________________________________________

___________________CAD Image____________________
/
/
/
/
/
/ *blank search field*
/
/ -------------------------------
/
/
/
/ __________________
/
/ / Search Button /
/
/ --------------------------------
/
/
/ ______________________________________________ /


Notes ___________________________________________

A couple of questions:
You say you want to return all records with a specific value in the part
number name. Where do you want them to be?
Is your form a single record form, a continuous form, or is it a sub form in
datasheet view?

Hello all....help

[quoted text clipped - 92 lines]

I tried to include as much info as i could think would be appropriate. Any
help would be so much appreciated. thank you

 

[mphotographer]

Ok. I got my search working.... I essentially scrapped all my coding before
and made it an "on enter" procedure.

My current code is:


Private Sub NameSearch_Ref_Enter()

If IsNull(NameSearch_Ref) Or NameSearch_Ref = "" Then
Me.FilterOn = False
Me.Filter = ""
Else
Me.Filter = "[Name] like '*" & NameSearch_Ref & "*'"
Me.FilterOn = True
End If

End Sub

 

[Guest]

If I understand correctly, the results of the search will be the recordset of
the current form (test 2). In this case, filtering the form will do the
trick. The code below is untested "air code", so if you get errors or your
monitor melts, I am not responsible

Notice some name changes to avoid problems. For example, Name is an Access
reserved word and can cause problems.

Private Sub NameSearch_Cmd_Click()
Dim Name As String 'Name of record sought

'Enable error handling
On Error GotTo Err_Find_NameSearch_Cmd_Click

If IsNull(Me![NameSearch_Ref]) Then
MsgBox "Please enter part name.", vbOKOnly + vbExclamation, _
"Missing Part Name"
Me.NameSearch_Ref.SetFocus
Else
If IsNull(DLookup("Name", "tABLE1", "Name Like '*" & _
Me.NameSearch_Ref & "*'") Then
MsgBox "No part " & Me![NameSearch_Ref] & " found.", vbOKOnly + _
vbExclamation, "Invalid Part Name"
Else
Me.Filter = "[Name] Like '*" & Me.NameSearch_Ref & "*'")
Me.FilterOn = True
Me.[Name].SetFocus
End If

'Erase part name entered
Me![NameSearch_Ref] = Null
End If

'Exit procedure
Exit_Find_NameSearch_Cmd_Click:
Exit Sub

'Display error message and exit
Err_Find_NameSearch_Cmd_Click:
MsgBox Err.Description
Resume Exit_Find_NameSearch_Cmd_Click

End Sub 'Find_NameSearch_Cmd_Click

--
Dave Hargis, Microsoft Access MVP

Ok...a quick look at my form is below... I would like the result of my search
to fill in the information in the form and be in a record 1 of xxx format.

Customer _______________________________________

File _______________________________ Rev _______

Name __________________________________________

___________________CAD Image____________________
/
/
/
/
/
/ *blank search field*
/
/ -------------------------------
/
/
/
/ __________________
/
/ / Search Button /
/
/ --------------------------------
/
/
/ ______________________________________________ /


Notes ___________________________________________

A couple of questions:
You say you want to return all records with a specific value in the part
number name. Where do you want them to be?
Is your form a single record form, a continuous form, or is it a sub form in
datasheet view?

Hello all....help

[quoted text clipped - 92 lines]

I tried to include as much info as i could think would be appropriate. Any
help would be so much appreciated. thank you

 

[mphotographer via AccessMonster.com]

Thank you for your help.
I was able to get my search working