Full Version: Really simple! Problem 'searching' for records
UtterAccess Forums > Microsoft® Access > Access Forms
Kiel_uk
Hey all.
Ok, i'm sure this is probably really easy, and you guys/girls will prob answer within minutes, but its been botherin me for ages!
All i wanna do, is have a form, with 3 text boxes on it. Say txt_name, txt_number, txt_telno. I have two tables, tbl_names, and tbl_numbers. In tbl_names, there is two fields: name & number. In tbl_numbers is also two fields: number & telno.
Ok, i wanna open the form, enter a name into txt_name, and i want the appropriate related number & telno to appear in the txt_number and txt_telno. At the moment, each time i enter a name, it just tries to create a new record, or ammend one.
Hope this makes sense - this isnt all i need my database to do, however this will be a great help, and will get me past this little problem!
Please Help!
danishani
Welcome to UA!
Why u need the telephonenumber in a seperate table to begin with?
Normaly one person have one phone number and a mobile number right?
Daniel
argeedblu
Welcome to Utter Access Discussion Forums!!
You don't say how your tables are related to each other and you haven't indicated which fields are the primary keys of the two tables. Can you zip a copy of your database and post it as an attachment to your reply? That way we can have a better idea of what the source of your difficulty is. You should indicate in your post the relevant table, field, and form names. And you should remove any sensitive or confidential data from the copy you post.
Glenn
argeedblu
Daniel,
If a person can have more than one telephone number, that is sufficient reason to have them in a separate table. Otherwise the address/contact data would not be normalized.
Glenn
danishani
Glenn,
agree fully with you on normalization perspective, but like I said, normaly one person have only one phone number and a mobile number...
Daniel
dannyseager
I've got 2 work numbers, a home number and a mobile number... I know one shopperholic/techno lover that has 4 mobile phones!!!
argeedblu
Isn't that two telephone numbers, Daniel? In the telephone systems that I am familiar with, if a person's mobile number is for their cell phone, the number is essentially similar in format to their fixed land-line number.

Glenn
Edited by: argeedblu on 09.20.04.
argeedblu
What? No fax number? frown.gif

Glenn
Edited by: argeedblu on 09.20.04.
danishani
Glenn I got myself with that answer ohyeah.gif
Thanks...
Daniel
danishani
Yeah, got it Danny... wink.gif
aniel
argeedblu
It's just five hours earlier in the day for me, Daniel. I thought you might be having an 'off moment.' frown.gif
Glenn
danishani
Well I guess you are right...its time to switch of my computer... smirk.gif
Cheers Glenn... o!
Daniel
Kiel_uk
Ok, well heres the DB. Its not fone numbers and names, was just using that as an example. The only form im using at the moment is frm_Create400...
It says sumthin in MS Help files about putting Form![frm_Create400]![txt_cs] - but this still pops up a box askin for the parameter.
Hope this makes it simpler? (The data in the two tables is not accurate by the way, just made it up now!)
dannyseager
I forgot my fax number's frown.gif
argeedblu
Since you were "just using" telephone numbers and names as an example, I am completely lost as to what problem you are really tryiing to deal with.
ow, in your latest message you are referring to the query requesting a parameter (despite what you are expecting?) Is that the problem? If it is, then, the source of the problem is that the criteria must refer to an actual control on an open form. Your form frm_Create400 does not appear to have an object named text5. So you can't use the query as the datasource for the form from which you are trying to get the parameter. You will need a different form with a textbox or other control (I usuually use a combo box in this sort of situation) where the user can specify the value that the query will use as its criteria when the form which is bound to it opens.
Glenn
Kiel_uk
Ok, i renamed the text5 to txt_cs but forgot to update it in the query.
When i have the form open (that with txt_cs on it), i want to enter a value into txt_cs, and for txt_type and txt_mtow to update accordingly...
I thought this would be easy, but it aint!
argeedblu
Notice also that your form is bound to the query that has a criteria referring to a field in the form. So things are going around in circles. If you simply want to update the table, you don't need the criteria. If you want the query to lookup up a record based on the criteria, you have to have the text box to which the criteria refers an a different form.
Here's the way things work. When you open a form that is bound to a query, the form calls on its query to get its data. Text boxes and other controls on the form are bound to the query fields that are their data source. The query fields, in their turn, are bound to the table fields that you have included in the query. Updating a control on a form will send the information through the query to the relevant field in a table. Right now, your query is asking for a parameter because you have a parameter criteria in the query.
Glenn
Kiel_uk
So there is no way at all that I can open a a form, enter a value (a callsign) into a box on it, and for the callsigns respective details (type & mtow) to appear in the other 2 boxes on the same form?
argeedblu
I'm still not entirely clear on what it is you are trying to do. However, if you are wanting to see a record with all related records from another table then you can use a form and subform. The main form is bound to the main table and the subform is related to the main form using the fields that are used to relate the two tables.
Glenn
Kiel_uk
I dont know how better i can explain it, sorry.
want to open up a form, with three text boxes on it {txt_cs} {txt_type} {txt_mtow}
I have two tables {tbl_callsigns} with fields [Callsign] [Type] (Callsign Primary Key)
{tbl_mtow} with fields [Type] [MTOW] (Type Primary Key)
I want to open the form, and manually type in a value into {txt_cs}.
On exiting this txtbox, I want {txt_type} to be filled in by the DBase searching in {tbl_callsigns} for the coresponding type. I then want {txt_mtow} to be updated by taking the record from {tbl_mtow} who's [Type] matches that already input into {txt_type}.
I dont want more than one form, I just want this to happen on the one form. Basically, every aircraft has a callsign, type, and maximum take off weight. I just want this form to find them, but without using an external form. Maybe if i dont attach the form to the query, but run the query on exiting the box?
I have a spreadsheet which does this already, however wont do everything else i need it to do, which is why im trying to put it all into a single database!
argeedblu
I have made some revisions to your sample database. You will find shortcuts to the tables, query, and form as I have revised them in the favorites group in the database window.
The form as you had it will not display records because its data entry property was set to true. A data entry form does not display existing data when it is opened. The form is blank and is used only for entering for enteriing new records. With the property set to false, the form should at least dispaly the results of the query. However, you had included a criteria in the query which, as I indicated previously, would cause the query to open a paramenter message box to resolve which record to display. If you want all records to be available to the form, the query should have no criterial
I have revised the tables so that the have autonumber primary key that are indepedant of any real world data. Primary keys should serve only to identify records and should not use any real world values, sometimes referred to as natural keys. In general natural keys should be avoided when you need a primary key. I have removed the redundant type field from the MTOW table. The relatiohship between the two tables allows you to access the relevant MTOW value.
I have also revised the query to include only the fields that are needed by the form.
Finally, I have added a seach combo box to the form that lets you lookup the call sign and navigate to the relevant record.
HAs much as possible I have tried to work within the structure you have suggested. However, the structure of a database should reflect the real world entities that the database is modelling. The structure should also be desiged to avoid duplicate data. There are some general rules of database structural design known collectively as "normalization" with which your should become familiar. In order to function correctly and be as trouble-free as possible these rule should be adhered to religiously.
Anyway, I think you will find the form as I have revised it does what you need it to do.
Glenn
Kiel_uk
Yeah, it kinda does, thanx.
owever....
There's gonna be hundreds, if not thousands, of records in there, and i dun wanna have to use a drop down combo box to select the one i want! I just wanna be able to type in the callsign. Even if i have to press a buton or sumthin to execute a macro or VBCode.
Also, the information it pulls up doesnt need to be saved, only viewed for printing... Does this help at all?
argeedblu
The combo boxes uses code to search for the relevant record (you can find it by looking at the on_click function of the combo box. You could use this code for the after update event of a text box or the click event of a command button, to accomplish the same thing as long as you adjust the code to refer to the text box where the user types the call sign. You will need to adjust the code to take care of cases where the user types an unknown call sign.
Glenn
Kiel_uk
Sorry to be a pain Glenn, but i've been fiddling with that code, and i cant make it work!
I keep getting type mimatch errors, I think because your combo box was searching for a number (idsCallSignPK) whereas I need to search by string (contents of txt_cs)
The answer is probably ryt in front of me, but I just cant get it! Sorry.
argeedblu
Actually, the problem is my error, I forgot that you just want to look up a particular call sign. The combobox code uses the record primary key to do the search. To search for a value in a text field, you could open a filtered recordset whose source is:
Select * from tablename where search field =" & "'" & me!txt_cs & "'"
Substitute your table and relevant field names for the italicized parts of the statement. You need to have quotes around the me!txt_cs in the recordsets source string. "'" is a sequence of " followed by ' followed by ". You can then populate the rest of your form from the recorset results.
If you need the code to open a recordset, I can post it later today but right now I have to fight some aligators.
Sorry to have led you astray.
Glenn
Kiel_uk
Thank you so much for helping Glenn - It does what I require now!
If entering a callsign returns no results, it jst leaves the other two boxes blank.
What i want it to do is be able to tell when there are no records returned - ie, when the other two boxes remain blank. I tried
If txt_type = "" then ... But even though the box is empty, it doesnt work!
argeedblu
Super!!
If your are using the recordset approach I suggested then you can test the recordcount for zero which would mean there is no matching record.
Glenn
Kiel_uk
I used:

Me.RecordSource = "Select * from qselTypeAndMTOW where Callsign =" & "'" & Me!txt_cs & "'"

Would I then use

If Me.RecordCount = 0 Then ... ?

This doesnt seem to work

And if I have a Public Sub defined in a form, can it not be accessed by another form? Where would I have to declare it for me to be able to access irt from all forms?
Edited by: Kiel_uk on 09.23.04.
argeedblu
In that case I think you should be able to test the text box for null. Since there is no record the value of the text box should be not an empty string.
If isnull(me.txt_cs) then ...
Glenn
Kiel_uk
Is there anything you don't know?
Thanks for all your help Glenn, it really is much appreciated.
argeedblu
You are very welcome. And yes, there is a great deal I don't know. Even for what I do know, I can't always lay my fingers on what I need when I need it. Sometimes with Access it seems that every time I learn something new I learn of two or more things that I need to learn.
nyway it sounds like you now are able to accomplish what you wanted to do. Congratulation!
Glenn
Kiel_uk
Yes, I'm well on my way to creating the database I require.
oads more little problems, however i'm just gonna battle on and try work my way through or round them!
Thanks once again for your help Glenn.
kapeller
Hi
Have a look at this link. It may be what you are looking for.
Link
Cheers!!!
Lou
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.