Searching Question

[Jbelle7435]

I begain to use access a few days ago and tried learning thru the internet(i.e tutorials, tricks and what now. I def got the basics and do understand VB when its simple type stuff. My concern is how to search. There are so many different methods but I want to know whats best for my situation.

I just have a simple database(TABLE) that includes three columns:
Part Number, Part Name, Revision and I made a FORM that included a search text box with button and a subform that included a Part name text box and Revision text box.

1.The idea for searching had this setup of one main form to enter in the Part number and then have a subform to have the part name and part number come up??? IS this a good means for my situation.

2. Will I need a query when I want to add new data in the future or can I do that thru the table????

[Mclaren]

I have used Allen Browne's search example several times. easy to use and easy to figure out how to use.

http://allenbrowne.com/ser-62.html

[Jbelle7435]

Update:01

I followed the link and did not get any results: Here is my code below

Private Sub Command63_Click()
Dim strWhere As String
    If Not IsNull(Me.txtPNum) Then
        strWhere = strWhere & "([Pname] = """ & Me.txtPNum & """) AND "
    End If
    If Not IsNull(Me.txtPNum) Then
         strWhere = strWhere & "([Revision] = """ & Me.txtPNum & """) AND "
    End If
End Sub

a. Right now I have an Unbounded textbox for the search named "txtPNum" but I want # values(i.e 111,222,333) in there so would I do strWhere As Interger??

b. I have two boxes named "Pname" for Part name and "Revision" linked to the datasource(table) with the columns Pname,Revision based on each PNumber

so no luck yet

PS. I like this method without using the subform!

[Jbelle7435]

I am getting closer but the search is still not working:

new updated code:

Private Sub Command63_Click() Dim strWhere As String     If Not IsNull(Me.txtPNum) Then         strWhere = strWhere & "([Pname] = """ & Me.txtPNum & """) AND "     End If     If Not IsNull(Me.txtPNum) Then          strWhere = strWhere & "([Revision] = """ & Me.txtPNum & """) AND "     End If End Sub

I figured the Me.____ should be the same for each text box so that solved one issue and added even my data was showing up when the form opened but I fixed that by adding the clear form code when it opens up:
(Private Sub Form_Open(Cancel As Integer)....

but when I type values like 111,222,333 into the search box "txtpartsearch" which is unbounded I still don't get results in the two text boxes where the code should work. I don't know maybe how its referencing the part number text box...

Update 02

[Jbelle7435]

one unbounded textbox for searching named "txtpartsearch"
two bounded textboxes (1)"PName" ref. to PName (2)"Revision" ref. to Revision" the reference is a table with columns of PNum,PName,and Revision.

My latest command button code is

Private Sub Command6_Click()
Dim strWhere As String
   If Not IsNull(Me.txtpartsearch) Then
        strWhere = strWhere & "([PName] =  """ & Me.txtpartsearch & """) AND "
    End If
       If Not IsNull(Me.txtpartsearch) Then
        strWhere = strWhere & "([Revision] = """ & Me.txtpartsearch & """) AND "
    End If
End Sub

I can't go any further with some help!

[Jbelle7435]

HELP HELP HELP!! lol

I woke up this morning with a fresh mind to an idea I did previously..

1st idea that worked
1. Table1 with ID as primary Key with three other fields
Part Number, Part Name, Revision
2. query1 with all same fields
only difference was in my criteria for part number I had
[Forms]![MainForm]![Part Number]
3. Mainform that is UNBOUNDED has a Part Number textbox for searching,
and a SUBFORM WHICH IS BOUNDED by query1 that includes the Part Name and Revision text boxes
4. Command button in the Mainform with the command:
Me.Refresh

what I found out is the in this version the SUBFORM is looking in query1 while the mainform does not have to & the me.refresh command only works for the SUBFORUM!

2nd Idea that did not work WHY??
The reason why I know this is I tested a new version with.
1. THE SAME
2. THE SAME
3. New Mainform which is Bounded with a Part Number, Part Name , and Revision textbox and I bounded it to query1 and it DID NOT WORK!! with the Me.refresh command button!!!

How can I get my entire bounded form this time to work by searching a p/n and getting the part name and revision results in their respective textboxes without a subform???