Full Version: Data Retrieval
UtterAccess Forums > Microsoft® Access > Access Forms
cjg4444
I am trying to setup a system of forms and trying to make sure the correct info. is carried along. I could use some help in doing this.
I have a beginning form where a 9 digit number is given (numberForm). Whenever continue is clicked I would like the DB to look in a table, find the row that corresponds to the 9 -digit number and retrieve 3 fields from it.
I would then like Access to automatically open another form called studentInfo and put those 3 records in the form.
Will I need to do an underlying query and if so, how do I get a query to post to a form instead of the datasheet view? I am trying to use command buttons but I can't get the functionality right.
Any help would be very much appreciated. Thanks in advance.
Jack Cowley
You do not need to save the 3 fields data in your other form as it already exits in another table. If you properly structrue you database you should only need to save the 9 digit number as the foreign key in the table in question, though I would use the primary key of the record instead of the 9 digit number.
hope this isn't to confusing....
Jack
LittleViews
1 - verify that a 9 digit number was entered. If not, show message
2 - verify that the number exists in the table (SQL). If not, show msg. If so, continue
3 - assemble SQL statement "select field1, field2, field3 from myTable where ID =" & myNineDigit
4 - use above statement to open the new form
cjg4444
LittleViews-
I've tried that but whenever I input the 9-digit number into the form and hit continue, Access asks me to put in the parameter(the 9 digit number). How can I get it to automatically receive the 9 digit-number put in as a parameter for the SELECT statement?
LittleViews
Start with the first challenge. It looks like you have something misspelled. Delete the field that should contain the number. Close the form. Open the form. Put the field back in. Put in a 9 digit number and see if it works.
cjg4444
I tried but it is still asking for parameters. Here is my SQL statement.
ractice is the table where the info will be drawn from.
cajunNumberForm is the form where you would type in the 9-digit number
cajun# is the 9-digit number
SELECT practice.[cajun#], practice.FirstName, practice.LastName, practice.ucsID
FROM practice
WHERE ((cajunNumberForm.[cajun#]=practice.[cajun#]));
Thanks for all your help!!
LittleViews
What is the name of the field that holds your 9 digit number? Is it "cajun" ?? or is it "cajun#" ??
You might be trying something fancier than I normatlly do, but I suggest you try something like:
"select myNine, firstName, lastName, ucsID From practice where myNine = " & me.myNineField
cjg4444
What is the "&" for?
Jack Cowley
My apologies if I am stepping on LittleViews toes....
ry:
SELECT practice.[cajun#], practice.FirstName, practice.LastName, practice.ucsID
FROM practice
WHERE [cajun#]= [Forms]![cajunNumberForm]![cajun#]));
I am assuming you are using the cajun# in a form as the filter. Also, it is not a good idea to use the # character as it is a reserved character in Access. CajunNo would be a better choice..
The above code has NOT been tested but pasting it into the query builder should help you get the correct syntax.
hth,
Jack
cjg4444
YES!! that worked. Thanks. I just have one more problem. Iis there any way to put the fields found by the query into a form that is already made? I would like to add more info to these few fields and save it all in another table. I know it's redundant but that's what the boss wants.
Jack Cowley
If you set up your structure correctly the boss will not know that the data is not redundant. If the boss knows Access then he should know the rules, but since you are the Access expert it will be up to you to setup the db properly and, if necessary, explain the facts of life to the boss...
That is my 3 cents worth on how you should do the database...
Jack
LittleViews
That # threw me, too and my toes don't hurt, Jack. Some further comments:
My recommendation is to drop the table name (as in table.fieldname) completely from the query and with respect to referencing the formfield with the critical 9pack, possibly "me.fieldname" would have done it.
LittleViews
You might be in over your head. Then, maybe not.
If you have an underlying form and a popup form, it is possible to populate both in the same action.
Note that when two forms are alive at the same time, all the information is in memory. But there are a lot of things to consider. What happens if you look for or try to pass information to a form that was closed? You need to check for that, or see to it that that condition does not happen.
Best to take things one step at a time. You openned your 2nd form successfully. What is your next problem?
Note that I was working with a programmer who put _? at the end of his field names that was just insane. Those fieldnames could NOT be referenced without brackets [my_field_?] around them. The # might have caused a similar thing.
Jack Cowley
Phew! Glad to hear that I missed your toes when I jumped in, uninvited!
ack
cjg4444
I guess the next problem would be to get the next form to open with the query results automatically put in to their respective fields.
Thank both of you so much!
Jack Cowley
I do not understand what you mean by your statement. Are you wanting to put data into a new record in the next form or do you just want the next form to display the results of a query?
ack
cjg4444
I guess both, I would like the next form to display the results of the query, and there will be empty fields that a desk worker will fill in , and this whole record (the query results and the new information) will be saved to a table.
Jack Cowley
This is getting to a point that it is difficult for me to imagine what you want to do. Can you create a small demo (or send the database) of what you are trying to do so I can see what the query does and what needs to be added to a table besides the results of the query? I have Access97, 2000 and 2002 so if you can do this then compact and zip the demo and post here (500k max size).
ack
cjg4444
I think I am getting it. I appreciate all the help though! I didn't think anyone would actually try and help, this site is very helpful.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.