By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,479 Members | 1,622 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,479 IT Pros & Developers. It's quick & easy.

searching a specific record

P: 16
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.

------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3. ' Move crusor to Drawing number
  4. DrawingNumber.SetFocus
  5.  
  6.  Will it be like this to prompt user to enter drawing number?
  7. Forms![2008 Drawing].[Drawing Number] = [Drawing Number:]  
  8.  
  9. End Sub
  10.  
  11. ----------------------------------------------
  12. Public Sub cmdSearch_Click()
  13.  
  14. ' SQL variable
  15. Dim mySQL As String
  16.  
  17. ' Select everything from 2008 Elec table and query the drawing number user provided
  18.  
  19. mySQL = "SELECT * FROM [2008 Drawing]"
  20.  
  21. DoCmd.RunSQL mySQL 
  22.  
  23. End Sub
  24. ---------------------------------------------
  25. But then, I realize that RunSQL can't run "SELECT" statement. 
  26.  
  27. 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". 
  28.  
  29. Anyway, I changed code to the following because RunSQL can't do "SELECT":
  30.  
  31. -------------------------------------------------
  32. Public Sub cmdSearch_Click()
  33.  
  34. ' SQL variable
  35. Dim mySQL As String
  36. Dim con As Object
  37. Dim recSet As Object
  38.  
  39. Set con = Application.CurrentProject.Connection
  40.  
  41. ' Select everything from 2008 Elec table
  42. mySQL = "SELECT * FROM [2008 Drawing]"
  43. ' Drawing Number is provided by user
  44. Set recSet = CreateObject("ADODB.Recordset")
  45. recSet.Open mySQL, con, adOpenStatic, adLockOptimistic, adCmdTable
  46.  
  47. 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.
Aug 20 '08 #1
Share this Question
Share on Google+
20 Replies


P: 16
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.
Aug 20 '08 #2

NeoPa
Expert Mod 15k+
P: 31,709
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.
Aug 20 '08 #3

P: 16
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Year_AfterUpdate()
  2.  
  3. ' If user changes the year, check the last 2 digits
  4.  
  5. ' If it is '08
  6. If Right(Me.intYear, 2) = "08" Then
  7.  
  8. ' Close Current Form
  9. DoCmd.Close acForm, Me.Name, acSaveNo
  10.  
  11. 'Open 2008 Form
  12. DoCmd.OpenForm "Electrical 2008", acNormal
  13.  
  14. ' If it is '07
  15. ElseIf Right(Me.intYear, 2) = "07" Then
  16.  
  17. ' Close Current Form
  18. DoCmd.Close acForm, Me.Name, acSaveNo
  19.  
  20. 'Open 2007 Form
  21. DoCmd.OpenForm "Electrical 2007", acNormal
  22.  
  23. ' If it is '06
  24. ElseIf Right(Me.intYear, 2) = "06" Then
  25.  
  26. ' Close Current Form
  27. DoCmd.Close acForm, Me.Name, acSaveNo
  28.  
  29. 'Open 2006 Form
  30. DoCmd.OpenForm "Electrical 2006", acNormal
  31. .
  32. .
  33. .
  34. 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.
Aug 20 '08 #4

NeoPa
Expert Mod 15k+
P: 31,709
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.
Aug 20 '08 #5

P: 16
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!
Aug 25 '08 #6

NeoPa
Expert Mod 15k+
P: 31,709
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?
Aug 25 '08 #7

P: 16
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.
Aug 26 '08 #8

NeoPa
Expert Mod 15k+
P: 31,709
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?
Aug 26 '08 #9

P: 16
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?
Aug 26 '08 #10

NeoPa
Expert Mod 15k+
P: 31,709
Try something like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Drawing_Number_BeforeUpdate(Cancel As Integer)
  2.   Dim strDrawingNo As String
  3.  
  4.   strDrawingNo = Me.[Drawing Number]
  5.   Cancel = True
  6.   Me.Filter = "[Drawing Number]='" & strDrawingNo & "'"
  7.   Call Me.Requery
  8. End Sub
Aug 27 '08 #11

P: 16
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.
Aug 27 '08 #12

NeoPa
Expert Mod 15k+
P: 31,709
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) :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.   Me.Filter = "[Drawing Number]='" & Me.txtSearch & "'"
  3.   Call Me.Requery
  4. End Sub
Aug 28 '08 #13

P: 16
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) :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.   Me.Filter = "[Drawing Number]='" & Me.txtSearch & "'"
  3.   Call Me.Requery
  4. 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.
Sep 8 '08 #14

P: 16
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function yearCheck()
  3.  
  4. Dim txtDwgNum As String
  5. Dim whichYear As Integer
  6. Dim txtSection As String
  7. Dim intYear As Integer
  8. Dim newYear As Date
  9.  
  10. newYear = DateSerial(whichYear, 1 ,1) ' if it is Jan 1st
  11.                 intYear = whichYear
  12.  
  13. ' Hard code the fixed numbers
  14. txtDwgNum = "000"
  15. txtSection = "50"
  16.  
  17. ' Assign the numbers to the new record 
  18.  
  19. Me![Drawing Number] = intYear & "txtSection" & "txtDwgNum" 
  20.  
  21.  
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?
Sep 8 '08 #15

NeoPa
Expert Mod 15k+
P: 31,709
Like many other threads tonight I can only offer to post another day.

This is interesting and there are solutions, but my brain is not up to anything complicated tonight :(

Feel free to bump the thread if you hear nothing from me tomorrow.
Sep 8 '08 #16

NeoPa
Expert Mod 15k+
P: 31,709
Right, now that I seem to have my mental faculties back and working again, I will try to answer some of your points. There are a few, so I will answer thm as I get to them...

New Records :
There is a wizard that you can select when creating a ComboBox, that creates a control where you can select a record from the list, and this record will then be displayed on the form. This will not handle adding new records, but will provide VBA code which you can manipulate to use a search (rather than a simple match). From here it's not too much of a stretch to change it to go to a New record if the search criteria TextBox is blank. Let me know if further explanation necessary for this, or if you get stuck while trying it out.
Sep 9 '08 #17

NeoPa
Expert Mod 15k+
P: 31,709
More New Records :
To prepare the Drawing Number TextBox (bound), which we will call txtDrawingID for now, I suggest you produce a function which returns the valid value. I don't have the MetaData of your table so can only help in general terms here.

First produce the first part ("0850" for 2008 I believe). Next use a DMax() to search in your table for the highest [DrawingID] value starting with "0850". A Null will be returned if none yet exist. Take the last three characters (Use Nz() to return "0" if Null), then convert that string to a number. Format this number into three digits and append it to your "0850" string. This is the string value your function should return.

Let me know how you get on with this.
Sep 9 '08 #18

NeoPa
Expert Mod 15k+
P: 31,709
I think my last two posts lead you to a way where your other complications disappear. As such I won't go into your last posts (Normally I would try to answer all raised points). In this case I think (at the moment at least) that it would be a waste of time.
Sep 9 '08 #19

P: 16
I think my last two posts lead you to a way where your other complications disappear. As such I won't go into your last posts (Normally I would try to answer all raised points). In this case I think (at the moment at least) that it would be a waste of time.
It is somewhat true that your post pointed me to the place where I should spend more time.

Anyway, thanks for even posting a reply.
Sep 9 '08 #20

NeoPa
Expert Mod 15k+
P: 31,709
I can still answer questions, I simply didn't feel it made much sense to cover those at this time. If you have further difficulties just post :)
Sep 9 '08 #21

Post your reply

Sign in to post your reply or Sign up for a free account.