Ok...this is kind of confusing, but I can't seem to find any other helpful posts, so maybe someone will be able to help me! I have two forms. The first form allows user to input information into the database. On this form, there is a button, 'Equip. ?', that opens up another form. This second form is linked to a query and basically does a "search" of the Equipment table to find the corresponding Equipment Descriptions associated to their appropriate Equipment ID. The search works great, and all the records found in the search can easily be scrolled though on the form until you find the right Equipment ID that you need. My problem is that I need a button, code, whatever.....that selects the Equipment ID you need on the SECOND form and fills in the appropriate field on the FIRST form. Can this be done?? I've seen other posts similar to this question, but no one has posted a solution.....so I guess I'll try. Please help me!!!
Select record on 2nd form to display in 1st form
- Thread starter ksgirl
- Start date
OK.....I'm not sure where I would put this:
Forms!Form1.Form!EuipmentID = Me.EquiupmentID.
This would be in the On Click event of a button.
On the second form? I'm not sure if this would work for me.
Secondly, I'm not use to using combo boxes. I have the query set up, so that it asks for the user to type in a description, such as *valve* or *pipe*, it searches the whole description field (that could be 10 words long) and returns all the fields that have those words in it. I've tried using the combobox wizard before.....trying to reference it to this query but I get a "No value given for one or more required parameters" error message. I don't know exactly what this means, but I do know that it might have something to do with the way I have the query search set up. Does that make sense? Anyway, I'm back to square one. Does anyone else have a suggestion?
Forms!Form1.Form!EuipmentID = Me.EquiupmentID.
This would be in the On Click event of a button.
On the second form? I'm not sure if this would work for me.
Secondly, I'm not use to using combo boxes. I have the query set up, so that it asks for the user to type in a description, such as *valve* or *pipe*, it searches the whole description field (that could be 10 words long) and returns all the fields that have those words in it. I've tried using the combobox wizard before.....trying to reference it to this query but I get a "No value given for one or more required parameters" error message. I don't know exactly what this means, but I do know that it might have something to do with the way I have the query search set up. Does that make sense? Anyway, I'm back to square one. Does anyone else have a suggestion?
I'm a little confused. Please more precisely explain things. Or, better yet, let me see if I understand what you're trying to do.
Form 1, click a button: Equip?
This runs a query that brings up a 2nd form that is a list of all Equipment referenced by ID? Right? Does the criteria for the query COME from the first form or is the query generic (meaning it always returns the same list, regardless of what you put in form 1)?
You then want the user to select the equipment ID that they need in form 2 and have the form close and populate that ID in the first form?
100% OMG YES!!!/Yes, mostly/No, kinda/Not even close/What planet are you on?
Form 1, click a button: Equip?
This runs a query that brings up a 2nd form that is a list of all Equipment referenced by ID? Right? Does the criteria for the query COME from the first form or is the query generic (meaning it always returns the same list, regardless of what you put in form 1)?
You then want the user to select the equipment ID that they need in form 2 and have the form close and populate that ID in the first form?
100% OMG YES!!!/Yes, mostly/No, kinda/Not even close/What planet are you on?
AHHHHHH! I know it sounds confusing, but it really isn't....at least I don't think so. OK.....It doesn't matter what you put in the first form. Basically it is blank and the user is prompted to fill in all blanks. If the user knows the equipment ID off the top of their head then they can input it into the blank, but if they have no idea what the number is they can do a search. So the click on the 'Equip. ?' button which sends them to the second form. Before this form displays anything, a message box appears and ask the user to input a description to search by. When they hit enter or 'OK' the second form opens up to the query results. There might be anywhere from one to twenty results appear, and the user is allowed to scroll through the list until they find the equipment ID they need. All I need is for the user to click another button (hopefully, or something along those lines) which will select the equipment ID they were looking at and place it in the original form. Does that make sense? Let me know if something doesn't....and I'll clarify. So what should I do???? Thanks for all your help.....at least I feel like I might be going somewhere with all of this! 
~Nicole
~Nicole
Ah, that makes much better sense. 
Unfortunately, I'm still somewhat of a n00b user, so my voodoo isn't too good yet. But, I'll do what I can. I'm sure one of the seasoned access vets can help you out. Their voodoo has much more power than mine.
Anyway, here's my "stab" at your problem. Not 100% sure this is possible... Instead of using a second form, write your query so that the criteria is user defined... when the query runs this will pop up one of those little "enter parameters" boxes. The user then enters the search criteria. Rather than the results populate into a new form, have them read to an unbound combo box on the same form. Then the user picks the one they want.
If this doesn't work... I think your answer lies somewhere in this concept. If I have time, I'll try and test it and see.
Unfortunately, I'm still somewhat of a n00b user, so my voodoo isn't too good yet. But, I'll do what I can. I'm sure one of the seasoned access vets can help you out. Their voodoo has much more power than mine.
Anyway, here's my "stab" at your problem. Not 100% sure this is possible... Instead of using a second form, write your query so that the criteria is user defined... when the query runs this will pop up one of those little "enter parameters" boxes. The user then enters the search criteria. Rather than the results populate into a new form, have them read to an unbound combo box on the same form. Then the user picks the one they want.
If this doesn't work... I think your answer lies somewhere in this concept. If I have time, I'll try and test it and see.
Ok, I understand where you are coming from, but I still don't think I can get rid of this second form. The query is user-defined, and the parameter box appears when the second form is opened. But if I get rid of the second form and change the query's criteria, then that message box will appear when the user opens the first form. I don't want this to happen. The user might know the equipment ID they want to select and fill it into the field automatically without having some kind of reference. So you don't want the parameter box to appear when they intially open the form. But, if they don't then they click the 'Equip. ?' button to search by description.
Maybe there is some other way of trying to do this. Is there anyway you can get rid of the second form and prevent the query from running until you click on that button which will then populate a combo-box with your search criteria? I don't really need that second form. I just need the search to happen and the only convenient way to do this search, that I've found, is to have that second form referenced to the query that has the user-defined criteria. But I don't want the user defined-criteria message box to appear unless they need to look up the correct equipment ID and description. Do you understand what I'm trying to say....let alone, what I'm trying to do??? I'm confused....someone please help me!!!
Maybe there is some other way of trying to do this. Is there anyway you can get rid of the second form and prevent the query from running until you click on that button which will then populate a combo-box with your search criteria? I don't really need that second form. I just need the search to happen and the only convenient way to do this search, that I've found, is to have that second form referenced to the query that has the user-defined criteria. But I don't want the user defined-criteria message box to appear unless they need to look up the correct equipment ID and description. Do you understand what I'm trying to say....let alone, what I'm trying to do??? I'm confused....someone please help me!!!
Old post....I know....but I still need some direction or ideas!! Does anyone have any suggestions? Simply I want to pick an 'equipment ID' from the second form to populate the first form's 'equipment ID field'.
??????
??????
Allow me an observation first: Hopefully, the blank controls you want to fill on your Fomr1 are NOT meant to update an underlying table. If they are, you are duplicating data in your database...
So I assume these are unbound controls.
There are probably various ways to achieve what you want. Here is one:
In Form 1 you have an invisible unbound control that will receive your Equipment ID once selected by users (let's call it txtEquipmentID) . Other controls are visible, unbound and their recodsource is set to a Dlookup that will get the info you are looking after, based on txtEquipmentID.
Ex:
=DLookup("[YourInfoField]";"YourTableName";"[Equipment ID]= " & [txtEquipmentID])
(this syntax assumes that EquipmentID is a numeric field
Your button on form1 opens form2 where the users select a field (I suppose in a continuous or datasheet form). Let's say that txtEquipmentID2 is the control that holds Equipment ID in that form.
On the command button OnClick event of form2, you would have somenthing like:
Dim frm As Form
'Create an form object corresponding to Form1
Set frm = Forms.item("Form1").Form
'Update the invisible txtEquipmentID control in form1 according to selected txtEquipmentID2 in form2
frm.Controls("txtEquipmentID") = Me.txtEquipmentID2
'Update all controls calculations in form1 (DLookup...)
frm.Recalc
'Free memory
Set frm = Nothing
'...
DoCmd.Close
Hope this helps
So I assume these are unbound controls.
There are probably various ways to achieve what you want. Here is one:
In Form 1 you have an invisible unbound control that will receive your Equipment ID once selected by users (let's call it txtEquipmentID) . Other controls are visible, unbound and their recodsource is set to a Dlookup that will get the info you are looking after, based on txtEquipmentID.
Ex:
=DLookup("[YourInfoField]";"YourTableName";"[Equipment ID]= " & [txtEquipmentID])
(this syntax assumes that EquipmentID is a numeric field
Your button on form1 opens form2 where the users select a field (I suppose in a continuous or datasheet form). Let's say that txtEquipmentID2 is the control that holds Equipment ID in that form.
On the command button OnClick event of form2, you would have somenthing like:
Dim frm As Form
'Create an form object corresponding to Form1
Set frm = Forms.item("Form1").Form
'Update the invisible txtEquipmentID control in form1 according to selected txtEquipmentID2 in form2
frm.Controls("txtEquipmentID") = Me.txtEquipmentID2
'Update all controls calculations in form1 (DLookup...)
frm.Recalc
'Free memory
Set frm = Nothing
'...
DoCmd.Close
Hope this helps
Last edited:
These are bound controls (on the first form). And yes I know this would duplicate data, but it is for a good reason. I have a table set up for just the "Equipment", this data cannot be changed. Here there is the equipment ID, the description, the location, etc.... And this is where the query is linked to when the "Equip. ?" button is clicked on. So the second form runs a search through the entire equipment table to come up with the appropriate ID for the description. On the first form, like I mentioned before, the controls are bound....because I want this information to store into the "Clearance" database table. (Clearances are issued according to equipment that needs to be fixed in the area, and this table is where you can find when, where, and what needs to be worked on.) All I need is after the equipment search is done, and the user has scrolled through the second form (it is not continuous) to see all the results and have found the one that they need, then they ultimately click on a button that returns that value to the field on the first form. So my next question is how do I go about this since the controls are bound on the first form? Do I need to clarify anything?? Please, anyone...I'm desperate!
ksgirl,
Sorry to insist, but your justification is far from convincing. I would say you just need to store in your clearance table the equipmentIDs, and link that table to your 'Equipment list' table, so that you can retrieve the other infos whenever you need (using a query for example).
BTW, it would work just the same than I described with bound controls, provided tha tyour form is based on your Clearance table. But I urge you to consider my suggestion.
Sorry to insist, but your justification is far from convincing. I would say you just need to store in your clearance table the equipmentIDs, and link that table to your 'Equipment list' table, so that you can retrieve the other infos whenever you need (using a query for example).
BTW, it would work just the same than I described with bound controls, provided tha tyour form is based on your Clearance table. But I urge you to consider my suggestion.
Last edited:
I'm sorry it isn't convincing for you. Let me clarify..... the Equipment table has the following: Equipment ID, Description, Location, Area Code, Plant, and Position fields, but the Clearance table only has the Equipment ID field (and none of the other Equipment fields on it) along with other necessary fields (approx. 16 fields) to complete the Clearance table. Does this make sense? This field (Equipment ID) is linked between the two tables! I have made this second form for ease of the user. They might not know the equipment ID to fill into the first form, so therefore they do a search......click on the "Equip. ?" button....fill in appropriate description into message box to do a search....second form brings up all equipment related to that description....Now how do you have the user select which equipment they want and have it fill in the first form? I thought this second form approach would be easier, would you suggest something else? I've tried a combo box, a subform, etc..... but I need the user to input the description criteria to search by......so I'm left with this two form method. I'm starting to think it can't be done. I will attempt to try the last suggestion to see if it will work, but I'm not convinced it will do the job!
Thanks for your help!
~Nicole
(With a little trial and error, I was able to get what I want, to work! I'm still not sure if it is going to do everything I want exactly, but thanks for everyones help! I appreciate it!!!)
Thanks for your help!
~Nicole
(With a little trial and error, I was able to get what I want, to work! I'm still not sure if it is going to do everything I want exactly, but thanks for everyones help! I appreciate it!!!)
Last edited:
Your approach (search form) is fine and makes perfect sense, and I am happy you got it to work.
Moreover you are telling me that your Clearance table does not duplicate fields from your Equipment table, which answers my question and shows that my concerns were not relevant.
This is somewhat contradictory with your previous statement that 'These are bound controls (on the first form). And yes I know this would duplicate data, but it is for a good reason', but I guess from the above that it comes from some misunderstanding about what I had in mind when I asked if the blank controls on your Fomr1 were bound and meant to update your underlying table...
Moreover you are telling me that your Clearance table does not duplicate fields from your Equipment table, which answers my question and shows that my concerns were not relevant.
This is somewhat contradictory with your previous statement that 'These are bound controls (on the first form). And yes I know this would duplicate data, but it is for a good reason', but I guess from the above that it comes from some misunderstanding about what I had in mind when I asked if the blank controls on your Fomr1 were bound and meant to update your underlying table...
