I am trying to get a form that has a table in it that the user can search through and also be able to click on any of the entries to open it up in its own form for editing. This seems like it should be pretty easy, but it is giving me a little trouble. I am thinking that this should be a form with a subform of my query. Then as far as trying to let the user click on a particular line in the table to open it as a form for editing, I am a little stumped. Thank you for any help.
Need help with a query in a form
- Thread starter coolcps
- Start date
- Local time
- Tomorrow, 04:23
- Joined
- Jan 20, 2009
- Messages
- 12,895
Place a subform on your main form. If you follow the Wizard, the subform Source Object will be another form displaying as DataSheet. This form will have a Record Source that is your query to be displayed for searching.
Place a text box on the main form. Make sure it does not have the same name as any field in a Record Source that may be used for the main form. Set its Control Source to be the Current Record of the subform.
Control Source: =[Forms]![MainFormName]![SubformName].Form![FieldName]
The text box will follow the changes in the subform. Add as many text boxes as required to define the unique record you want to edit in the second form. You can set them to not visible if you like.
Place another subform which will be used for the editing of the data. It will have a Source Object as yet another form whose Record Source is the table or updatable query you are going to edit. Set the default view of this subform as Form.
On the main form select the editing subform object. Set the Link Master Fields as the text box. Set the Link Child Fields as the matching field in the editing subform.
Enter as many Master and Child fields as required separated by a semicolon. There must be a one to one match of the two lists.
Now your editing form will follow the current record in the table displayed above.
If you want to open a new form instead of using the edit subform simply use the text boxes as the criteria in the Select query.
Forms!MainformName.TextBoxName
Place a text box on the main form. Make sure it does not have the same name as any field in a Record Source that may be used for the main form. Set its Control Source to be the Current Record of the subform.
Control Source: =[Forms]![MainFormName]![SubformName].Form![FieldName]
The text box will follow the changes in the subform. Add as many text boxes as required to define the unique record you want to edit in the second form. You can set them to not visible if you like.
Place another subform which will be used for the editing of the data. It will have a Source Object as yet another form whose Record Source is the table or updatable query you are going to edit. Set the default view of this subform as Form.
On the main form select the editing subform object. Set the Link Master Fields as the text box. Set the Link Child Fields as the matching field in the editing subform.
Enter as many Master and Child fields as required separated by a semicolon. There must be a one to one match of the two lists.
Now your editing form will follow the current record in the table displayed above.
If you want to open a new form instead of using the edit subform simply use the text boxes as the criteria in the Select query.
Forms!MainformName.TextBoxName
Thanks for the help, but I am pretty new with Access and am having some trouble with the instructions.
I can get the text box to put up a number in relation to which row in the table is selected, but after that I am pretty stuck. The main thing I am concerned with right now is making a form pop up(with the selected record in it) by clicking on the row in the table (or a button to open selected record). Thanks again for any help.
I can get the text box to put up a number in relation to which row in the table is selected, but after that I am pretty stuck. The main thing I am concerned with right now is making a form pop up(with the selected record in it) by clicking on the row in the table (or a button to open selected record). Thanks again for any help.
- Local time
- Tomorrow, 04:23
- Joined
- Jan 20, 2009
- Messages
- 12,895
Sounds like you have managed to display the table in a subform of the main form and have a text box showing a field from the Currrent Record.
Is this text box sufficient to define a unique record? For example, a primary key that is unique to each record. If not then add boxes in a similar fashion until you could define a unique record using those fields.
Then make a new SELECT (default) query based on the table. In the "criteria" of the fields enter the names of the text boxs of the fields to match.
This names are in the style: Forms!YourFirstFormName.YourTextBoxName
When you run this query it should show the record you selected in the first form.
Now make your second form using the Wizard. Choose the new query as its Record Source ("based on"). Each time you open this form it should have the current record of the first form showing.
Make a button on the first form using the wizard. One of the available actions should be to open a form. Choose your second form as the form to open. Choose open in Form View and it should have a box for each field in the table showing the Current Record in the first table.
Is this text box sufficient to define a unique record? For example, a primary key that is unique to each record. If not then add boxes in a similar fashion until you could define a unique record using those fields.
Then make a new SELECT (default) query based on the table. In the "criteria" of the fields enter the names of the text boxs of the fields to match.
This names are in the style: Forms!YourFirstFormName.YourTextBoxName
When you run this query it should show the record you selected in the first form.
Now make your second form using the Wizard. Choose the new query as its Record Source ("based on"). Each time you open this form it should have the current record of the first form showing.
Make a button on the first form using the wizard. One of the available actions should be to open a form. Choose your second form as the form to open. Choose open in Form View and it should have a box for each field in the table showing the Current Record in the first table.
Let me use the names of my forms and what not to make it easier all around.
There is:
tbl_ASI (table that contains all the data)
frm_ASI (this is the form that people enter the info in, also the one I would like to pop up when a record is selected from the search)
qry_ASI (a query for the table)
frm_ASISearch (This is the main form for the search)
frm_qry_ASI (this is the subform inside of frm_ASISearch that shows as a datasheet)
Ok so theres all those guys. Now in the text box under frm_ASISearch I have the control source as (=[frm_qry_ASI].[Form].[CurrentRecord]). This will make the number of the selected row show up in the box. It seems what I would want to do is have the ID value show up in that box and use it to relate it to the frm_ASI that comes up so that record is shown. It seems like this could be done with an OpenForm macro and the SQL Where statement (does that seem right)?
GalaxiomAtHome, I am going to see what I can make happen with your last reply right now, I just wanted to put up some more info. Thanks for all of your help.
There is:
tbl_ASI (table that contains all the data)
frm_ASI (this is the form that people enter the info in, also the one I would like to pop up when a record is selected from the search)
qry_ASI (a query for the table)
frm_ASISearch (This is the main form for the search)
frm_qry_ASI (this is the subform inside of frm_ASISearch that shows as a datasheet)
Ok so theres all those guys. Now in the text box under frm_ASISearch I have the control source as (=[frm_qry_ASI].[Form].[CurrentRecord]). This will make the number of the selected row show up in the box. It seems what I would want to do is have the ID value show up in that box and use it to relate it to the frm_ASI that comes up so that record is shown. It seems like this could be done with an OpenForm macro and the SQL Where statement (does that seem right)?
So I guess I need to know how to make that show the ID.
GalaxiomAtHome, I am going to see what I can make happen with your last reply right now, I just wanted to put up some more info. Thanks for all of your help.
- Local time
- Tomorrow, 04:23
- Joined
- Jan 20, 2009
- Messages
- 12,895
It is a little tough without the context of your data. A key field in the table does make it easy but is not essential. Depends on the data.
For examle you might have a table with the fields FirstName, LastName, City, RegistrationDate. So long as two Bill Smith's from Birmingham didn't register on the same day you probably could select a unique record using these fields.
With a Key field you can get the right person with just one field because each record has a unique number. In such a case as above I would recommend a Key because you are probably going to refer to them over and over again. Also when two guys called Edward Murphy register on the same day it will stop your system falling over.
You can easily make a key field in the table design by adding an autonumber field and setting it as the key.
(Listen for the purists screaming. You can also do it by finding the largest number in the key field and adding one to it when you insert a new record.)
Incidentally I am the same as user Galaxiom. I just need to ask a question at home one night and couldn't remember my password.
Greg
For examle you might have a table with the fields FirstName, LastName, City, RegistrationDate. So long as two Bill Smith's from Birmingham didn't register on the same day you probably could select a unique record using these fields.
With a Key field you can get the right person with just one field because each record has a unique number. In such a case as above I would recommend a Key because you are probably going to refer to them over and over again. Also when two guys called Edward Murphy register on the same day it will stop your system falling over.
You can easily make a key field in the table design by adding an autonumber field and setting it as the key.
(Listen for the purists screaming. You can also do it by finding the largest number in the key field and adding one to it when you insert a new record.)
Incidentally I am the same as user Galaxiom. I just need to ask a question at home one night and couldn't remember my password.
Greg
- Local time
- Tomorrow, 04:23
- Joined
- Jan 20, 2009
- Messages
- 12,895
Do you really want to pop up a totally separate form for the data entry?
You can put the both the search and data entry as subforms on the same main form if you want. It is actually slightly easier this way but either can be done without too much trouble. It does mean you wouldn't need a button as the second subform will track the first subform.
You can put the both the search and data entry as subforms on the same main form if you want. It is actually slightly easier this way but either can be done without too much trouble. It does mean you wouldn't need a button as the second subform will track the first subform.
Ive got the default autonumber ID field in the table.
This makes more sense. I have a form where you enter the data. On that form is a search button(frm_ASI), when you click this button another form pops up (frm_ASISearch) and contains the subform with all of the records in datasheet form. If you double click one of those records I would like the frm_ASISearch to close and the original frm_ASI (that was still open behind the search) to update with the data of the selected record. Does that make sense? Thanks again!
This makes more sense. I have a form where you enter the data. On that form is a search button(frm_ASI), when you click this button another form pops up (frm_ASISearch) and contains the subform with all of the records in datasheet form. If you double click one of those records I would like the frm_ASISearch to close and the original frm_ASI (that was still open behind the search) to update with the data of the selected record. Does that make sense? Thanks again!
Try putting this on the double click event for one of the fields in the popup form (or add a button with this on the click event)
Code:
Dim stDocName As String
Dim stFilter As String
stDocName = "frm_ASI"
stFilter = "ID = FORMS!frm_ASISearch!ID"
DoCmd.OpenForm stDocName, acNormal, , stFilter
DoCmd.Close acForm, "frm_ASISearchor, since the main form stays open you could also try:
Code:
Dim stFilter As String
stFilter = "ID = FORMS!frm_ASISearch!ID"
Forms!frm_ASI.Filter = stFilter
Forms!frm_ASI.FilterOn = True
Forms!frm_ASI.Refresh
DoCmd.Close acForm, "frm_ASISearchOf course that's assuming that the record source for both forms has the unique "ID" field as a key.
It should be fine that the Record Source for all of the forms is tbl_ASI, correct? That would satisfy them both having the same unique ID. I tried the code that you put up, but it always opens the form with the lowest ID number, not which one is currently selected. And shouldn't the stFilter code reference frm_qry_ASI, which is the subform displayed in a data sheet where the user is clicking?
When I use this as the code:
Option Compare Database
Private Sub ASI__DblClick(Cancel As Integer)
Dim stFilter As String
stFilter = "ID = FORMS!frm_qry_ASI!ID"
Forms!frm_ASI.Filter = stFilter
Forms!frm_ASI.FilterOn = True
Forms!frm_ASI.Refresh
DoCmd.Close acForm, "frm_ASISearch"
End Sub
I double click on the table to run that code and I get a box that pops up and says: "Enter Parameter Value" and then "Forms!frm_qry_ASI!ID". I can enter an ID number in the field and the frm_ASI will update with the corresponding information and the search will close. If i open the search up again through the frm_ASI then whatever I click on will take me to the same record (the ID that I entered).
Sorry if that was confusing at all, thanks for the help so far.
- Local time
- Tomorrow, 04:23
- Joined
- Jan 20, 2009
- Messages
- 12,895
change the line to:
stFilter = "ID = FORMS!frm_qry_ASI.Form!ID"
The original line is refering to a nonexistent control called ID on the form so it prompts you to enter a value. You need to refer to a field in the current record of the form, hence:
".formname.form!ID" rather than ".formname!ID"
On the first use the stFilter varaiable is set to the ID you enter and so it continues to use this value.
When I use this instead I still get the box popping up to enter the parameter. It seems that it does not recognize which record is selected within the subform frm_qry_ASI. No matter how I try it I cannot get it to recognize the selected record, I always get that prompt. Thanks for anymore ideas guys!
BTW here is the code in that I have for double clicking on the ASI field in the datasheet.
Last edited:
- Local time
- Tomorrow, 04:23
- Joined
- Jan 20, 2009
- Messages
- 12,895
The problem is we are referring directly to the subform object rather than the subform control on the main form that holds the subform object.
stFilter = "ID = FORMS!Mainformname!Subformcontrolname.form!ID"
stFilter = "ID = FORMS!Mainformname!Subformcontrolname.form!ID"
Sweet, OK so that works now, but the only problem is if I hit search again from frm_ASI it will only pull up the record that was clicked the first time, I need a way to wipe that value after it has been opened, so it will open the newly selected record. Thanks again!
- Local time
- Tomorrow, 04:23
- Joined
- Jan 20, 2009
- Messages
- 12,895
Sounds like you just need to remove the filter but I am still a little hazy on the structure of your forms so I am not sure of the event to trigger this.
However the code line would be:
Forms!frm_ASI.FilterOn = False
However the code line would be:
Forms!frm_ASI.FilterOn = False
Well I could not find the right place to put the
Forms!frm_ASI.FilterOn = False
But I changed it up so I went with the frm_ASI closing when you choose to search, this way it reopens it everytime with the selected record. Plus this makes sense if I want to put the search option directly on my main menu too. Thanks for all the help, I am sure I will have tons of other questions before I am done with this thing.
Forms!frm_ASI.FilterOn = False
But I changed it up so I went with the frm_ASI closing when you choose to search, this way it reopens it everytime with the selected record. Plus this makes sense if I want to put the search option directly on my main menu too. Thanks for all the help, I am sure I will have tons of other questions before I am done with this thing.
