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?

[NeoPa]

Try something like :

Private Sub Drawing_Number_BeforeUpdate(Cancel As Integer)
  Dim strDrawingNo As String

  strDrawingNo = Me.[Drawing Number]
  Cancel = True
  Me.Filter = "[Drawing Number]='" & strDrawingNo & "'"
  Call Me.Requery
End Sub

[tkip]

It looks very simple and I am sure I am thinking too hard to see solve a simple problem.

One question though.

How do I associate "Drawing_Number _BeforeUpdate" with a button called "Search"?

That's what I have on the form and that's how I want the operator to search. Type in the "Drawing Number" of interest and then click "Search" button. Then voila ...all the info for that particular drawing number is shown on the form.

So any more suggestion?

Thanks alot.

[NeoPa]

You don't. This is a solution to the problem clarified in posts #7 & #8 where you confirmed I had outlined the problem correctly. This scenario doesn't include a Search CommandButton.

Including a Search Button is a good idea, and is easily implemented (with a few small design changes). It's simply a different question than the one asked.

If you want to use a Search Button you cannot use the same control to specify which record to search for, as you use to display that value of the found record. At least I can think of no way to make that work.

What you need to do is to take the more standard approach of creating a separate, unbound TextBox control for entering the [Drawing Number] of the item to search for. I suggest this control, as well as the Search button, be shown in a separate area of the form from where the main record is displayed.

You still need to handle making sure that no-one can edit the data in the found record (This is for display only I understand). Instead of setting Cancel = True as I suggested before, I think simply setting the Locked property of each control to True would be as effective, as well as simpler.

Your code in the Search Button procedure would be more like the following then (assuming the button is named cmdSearch and the new control is named txtSearch) :

Private Sub cmdSearch_Click()
  Me.Filter = "[Drawing Number]='" & Me.txtSearch & "'"
  Call Me.Requery
End Sub

[tkip]

You don't. This is a solution to the problem clarified in posts #7 & #8 where you confirmed I had outlined the problem correctly. This scenario doesn't include a Search CommandButton.

Including a Search Button is a good idea, and is easily implemented (with a few small design changes). It's simply a different question than the one asked.

If you want to use a Search Button you cannot use the same control to specify which record to search for, as you use to display that value of the found record. At least I can think of no way to make that work.

What you need to do is to take the more standard approach of creating a separate, unbound TextBox control for entering the [Drawing Number] of the item to search for. I suggest this control, as well as the Search button, be shown in a separate area of the form from where the main record is displayed.

You still need to handle making sure that no-one can edit the data in the found record (This is for display only I understand). Instead of setting Cancel = True as I suggested before, I think simply setting the Locked property of each control to True would be as effective, as well as simpler.

Your code in the Search Button procedure would be more like the following then (assuming the button is named cmdSearch and the new control is named txtSearch) :

Private Sub cmdSearch_Click()
  Me.Filter = "[Drawing Number]='" & Me.txtSearch & "'"
  Call Me.Requery
End Sub

Sorry about the late reply. I solved the problem by doing some stuff you suggested and a few others.

Now, I can search for any record in the table with any number of parameters set by the user.

But adding new records is now a problem. Searching the database is something we want to do occasionally. We do, however, add records on daily basis. That "Adding New Record" which I thought will be simpler turned out to be a lot more complicated than I anticipated.

First of all, following the recommendations of other friends and perhaps from you too, I combined some tables into one if they are same type.

For example, I have tables called "Electrical 1999" all the way to "Electrical 2008". I did not partitioned the tables. It was how it was set up before I even got this job. Anyway, I was reluctant to start making tables that contains hundreds of records. Besides, "Electrical " is not the only type in the database. There are so many other tables for "Mechanical " etc... partitioned in the same way.

Anyhow, I did started from the beginning and rebuilt the tables by combining all the same types of drawing numbers tables into one big table. It did make searching a lot simplier, easier and faster.

But I do not know how to even proceed on adding new record. The problem is this. The drawing numbers in the tables are set as "Text" because drawing numbers do not start with "1".

This is the naming format - 00-50-000 (No dash). The number "50" is fixed. The first two digits refers to the current year and the last three is the actual drawing number.

So for example, "0850444" means that the drawing was created in the year "2008" in section "50" and the drawing number is "444".

When someone wants to add a new record, the drawing number will of course increment by one making it "0850445".

To do that:

(1) First I need to check the current date
- if it is "Jan 1st of whatever the current year is"
"increment the first 2 digits" of the drawing number and then "zero out the last three digits" (i.e 000) e.g 09-50-000

(2) If it is not the new year then
- newRec = previousRec + 1

Any suggestion suggestions or sample to refers to?

Thanks a lot.

[tkip]

Something just came to my mind and there is a flaw in the conditional statement.

Although I want to check if it is the new year, if that is the only condition that the VB codes checks, user will have problem creating new drawing number later on. Say, for example, someone wanted to create a new drawing number but there was no drawing done on the new year's day (usually it is the holiday anyway), then the VB will just simply add one to the previous record which is very bad.

Anyway, this is what I have in mind:

Public Function yearCheck()

Dim txtDwgNum As String
Dim whichYear As Integer
Dim txtSection As String
Dim intYear As Integer
Dim newYear As Date

newYear = DateSerial(whichYear, 1 ,1) ' if it is Jan 1st
                intYear = whichYear

' Hard code the fixed numbers
txtDwgNum = "000"
txtSection = "50"

' Assign the numbers to the new record 

Me![Drawing Number] = intYear & "txtSection" & "txtDwgNum"

I still haven't tried that option yet. If you have any comments, let me know. Also, how would I add one to the previous and place it in the newRed if "whichyear" is the same as current year?