searching a specific record

[tkip]

Hello everyone. I have been working on this DB for quite some time and still can't figure out a way to do what I want to do. It's a database that keep track of drawings and engineering change etc. Aslo, the tables for drawings are grouped by year.

I have a Main Form and on that main form, several cmd buttons to initialize sub forms are located. There are a quite a few tables and the names are quite simple. For example, for drawing that are produced in 2008 are called "2008 Drawing" and so on.

So on the main form, when I click on "Search Drawing" button, it assumes that the user is searching for the drawing in the current year which is 2008. Therefore, it brings up the form I created for "2008 Drawing". On this form, there are a lot of data related to the drawing: Drawing Number, Title, Draft Person, Year, etc.

Also, there are two CMD buttons called "Exit" which exits the current form and go back to the Main Form and "Search" which queries the data.

What I want to be able to do is to prompt the user to enter the "Drawing Number" which is bound to "Drawing Number" column of the "2008 Drawing" table. When I press the "Search" button, I want it to get the "Drawing Number" that users entered and query the query I created base on table "2008 Drawing". When the query is done, I want to populate the data back in the form "2008 Drawing". There will be only one record because all the drawing numbers are unique.

This is what I did and did not work unfortunately.

------------------------------------------------

Private Sub Form_Current()
 
' Move crusor to Drawing number
DrawingNumber.SetFocus
 
‘ Will it be like this to prompt user to enter drawing number?
Forms![2008 Drawing].[Drawing Number] = [Drawing Number:]  
 
End Sub
 
----------------------------------------------
Public Sub cmdSearch_Click()
 
' SQL variable
Dim mySQL As String
 
' Select everything from 2008 Elec table and query the drawing number user provided
 
mySQL = "SELECT * FROM [2008 Drawing]"
 
DoCmd.RunSQL mySQL 
 
End Sub
---------------------------------------------
But then, I realize that RunSQL can't run "SELECT" statement. 
 
I need to get the "Drawing Number" from user before I press "Search" button. That's why I was thinking of prompting user to enter the "Drawing Number" in "Form_current". 
 
Anyway, I changed code to the following because RunSQL can't do "SELECT":
 
-------------------------------------------------
Public Sub cmdSearch_Click()
 
' SQL variable
Dim mySQL As String
Dim con As Object
Dim recSet As Object
 
Set con = Application.CurrentProject.Connection
 
' Select everything from 2008 Elec table
mySQL = "SELECT * FROM [2008 Drawing]"
' Drawing Number is provided by user
Set recSet = CreateObject("ADODB.Recordset")
recSet.Open mySQL, con, adOpenStatic, adLockOptimistic, adCmdTable
 
End Sub

--------------------------------------------------
But the recSet.Open is giving me error.

All I want to be able to do is to be able to search one record at a time from the user input by pressing "Search" button. I also want to be able to switch between the forms by just choosing the "year drop down" list from the form. Say, if I choose "2007", it should close the form "2008 Drawing" and open up the "2007 Drawing" form and should automatically know to query the "2007 Drawing" table/query.

Any suggestion at all is really welcome.

Thanks.

[tkip]

My bad. I just realized that I gave the wrong information. The "Year" field is unbound and it is not a "drop down" list. It's just a field to enter int.

[NeoPa]

A couple of ideas :

1. Don't store your data in separate tables for years. Simply have a [Year] field in the single table to separate out the different years. This will make the design of your database far less complicated.
2. Let us know which line number the code fails on and the details of the error.

[tkip]

I added a few codes to make "Year" field works. It's ture that if I have year as a column in the table, it will make it simpler. I just don't want to start creating everything from ground up. My boos gave me the MS Access DB they have been using since 1999 which doesn't have any sort of form. The only way for them to seperate each drawing is by spearating "Type of Drawing" and "Year". There are a lot of tables. They way they separate is sort of simple. If the drawing or schematic is "Electrical Drawing" and the year is "2008", they call the table "2008 Electrical Drawing". If it is mechanical, they name it in the same way. So that's a lot of tables and way too many records and stuff to play with.

What I did was that I created a field call "Year" and open up the query according to the year entered.

Private Sub Year_AfterUpdate()
 
' If user changes the year, check the last 2 digits
 
' If it is '08
If Right(Me.intYear, 2) = "08" Then
 
' Close Current Form
DoCmd.Close acForm, Me.Name, acSaveNo
 
'Open 2008 Form
DoCmd.OpenForm "Electrical 2008", acNormal
 
' If it is '07
ElseIf Right(Me.intYear, 2) = "07" Then
 
' Close Current Form
DoCmd.Close acForm, Me.Name, acSaveNo
 
'Open 2007 Form
DoCmd.OpenForm "Electrical 2007", acNormal
 
' If it is '06
ElseIf Right(Me.intYear, 2) = "06" Then
 
' Close Current Form
DoCmd.Close acForm, Me.Name, acSaveNo
 
'Open 2006 Form
DoCmd.OpenForm "Electrical 2006", acNormal
.
.
.
etc

------------------------

What line am I having problem with?

Then, it's line "21" and "45"

Also, keep in mind that I am just guessing some of those codes and I still don't have a clue how to pass the user input data which in this case "Drawing Number" and then use that data to query the database. I then want the result to display (populate) on the form.

[NeoPa]

Right then.

I get that you are not in a position to redesign the database at this time. That's actually quite a common reason for having shoddy data structures (a perfectly good and practical reason of course).

Having said that, your question is quite involved and rather calls for me to get deeply involved in your project. This I like to avoid if possible. I don't see my role to diagnose and fix your overall problem.

If you can break your problems down into individual questions I can help with, then fine. Remotely diagnosing a database I can't see takes too much much time (generally many hours). The purpose of this site is mainly for you to post your questions after you've broken them down into fundamental issues.

Also, keep in mind that I am just guessing some of those codes and I still don't have a clue how to pass the user input data which in this case "Drawing Number" and then use that data to query the database. I then want the result to display (populate) on the form.

For instance, if you could explain what you mean by this I could probably help you. Unfortunately there are many interpretations of "how to pass the user input data". Remember that most of what you say is dependent on the context, and I don't have that so it needs to be much clearer than when explaining face to face.

[tkip]

Ok.

Forget about the multiple tables and stuff that I mentioned above.

Assume that the following is my current DB:

1) I have a table called "Electrical 2008"
(a) it has these fields in the table
Drawing Number, Title, Job Number, Resource, Project Number

2) I created a query based on the above table and it is called "Elec 2008 Query"

3) I also created a form called "Elec Form" with multiple text fields that includes all the field names from the table "Electrical 2008"
(a) The form has a button called "Search" that is supposed to query either the table or the query

(b)The control source for the text fields are bound to the table "Electrical 2008".

What I am trying to accomplish is to search a record from the form associated to the unique "Drawing Number".

So user will type in the "Drawing Number" in the form - which I set up as a "text" field. I know it should be "int" or "long". But the drawing numbers starts with "0" eg. 012345. If I set the field as "int", it omits the leading "Zero" and I can't let that happen. It's the naming procedure of the dept.

After the user keyed in the "Drawing Number" in the "Search Form", he/she will click on the "Search" button. Then everything associated with that unique "Drawing Number" should "populate" in the same form.

I don't know if it makes sense to you.

a) user enters the "Drawing Number" in the text fied in the form called "Elec Form"
b) Then cliks on the "Search" button
c) Everything related/associated/connected/tied to/linked to the "Drawing Number" user entered populate/display/show on the current form. It basically fills all the other text fields in the form other than the "Drawing Number" because user already provided that data.

How do I go about doing that?

Thanks!

[NeoPa]

Firstly, you're wrong to think a text field is wrong :D Actually it's 100% correct for that type of field. Having people designing systems where numbers as text (formatted to x positions) are used is where the problem lies. I'm afraid you're not in a unique position though ;)

Anyway, let me see if I have you clearly...

• You want a form to fit a table ([Electrical 2008] probably or query [Elec 2008 Query] if necessary).
• There will be five TextBox controls on the form to match the fields [Drawing Number], [Title], [Job Number], [Resource] & [Project Number].
• When the operator enters any data into the [Drawing Number] control on the form, you want, instead of the current record being updated, for the form to display the matching record?

[tkip]

Yes. I do not want to update the record.

1) When user enter data into "Drawing Number"
2) Display the mathcing record in the same form

So we are finally on the same page.

[NeoPa]

Right, in that case what I think you need is a simple form designed to edit a record from your [Electrical 2008] table.

For all controls other than [Drawing Number] have a BeforeUpdate() procedure that simply sets Cancel=True.

For [Drawing Number], have a BeforeUpdate() procedure that does that too, but in addition, it should set the Filter property of the form to reflect the entered value then call Me.ReQuery. This will have the effect of showing the selected record if a matching one exists.

Does this sound like it does what you need?

[tkip]

Yeah...kinda sound right expect that I don't know how to set filter so that the query takes the "Drawing Number" that user enters.

That is the thing I have been trying to resolve. Have a look at the code from my previous posts. You will see that I try implementing filter of some sort so that the "Drawaing Number" that the operator enters is set as the filter to either query the table or the query itself.

I realize that need to use Me.Requery to display the query result. But first thing first.

If I can't set the filter right, nothing is going to display properly on the form.

Can you give me an example if you dont' mind?