Access 2007, Form with with Combobox Filters and Unbound Text Box and Unbound combo box update of original record
Hello to all the Experts!!! Hope your weekend is great.
I've created a Form that has 2 Combo Boxes that look to 2 table in order to filter down to a particular record. [For the sake of this question let's say it's the 5th record of a particular Employee]
Once that occurs, on the Form the Manager is to Complete an Unbound Text Box for Comments and then Click a "Save" button. The Save button is Assigned to a Macro that has the following:
Condition ------------------------ Action ------------------------ Argument
{blank} ------------------------- OnError ------------------------- Next
{blank} ------------------------- RunCommand ------------------SaveReco
[MacroError]<>0 -------------- MsgBox ---------------------- =[MacroError].Description]
I get no errors but it is NOT updating the filtered record. It is updating whatever is the first record for that employee Not the filtered to record. Additionally, the Date & Time field is not being updated with the Now() default value when it updates that record.
Do I have to convert this macro to an "Event Procedure" for that Command button so that it will update the appropriate record? or is there a way in the Macro to take care of it updating the "Filtered" record.
I've created a Form that has 2 Combo Boxes that look to 2 table in order to filter down to a particular record. [For the sake of this question let's say it's the 5th record of a particular Employee]
Once that occurs, on the Form the Manager is to Complete an Unbound Text Box for Comments and then Click a "Save" button. The Save button is Assigned to a Macro that has the following:
Condition ------------------------ Action ------------------------ Argument
{blank} ------------------------- OnError ------------------------- Next
{blank} ------------------------- RunCommand ------------------SaveReco
[MacroError]<>0 -------------- MsgBox ---------------------- =[MacroError].Description]
I get no errors but it is NOT updating the filtered record. It is updating whatever is the first record for that employee Not the filtered to record. Additionally, the Date & Time field is not being updated with the Now() default value when it updates that record.
Do I have to convert this macro to an "Event Procedure" for that Command button so that it will update the appropriate record? or is there a way in the Macro to take care of it updating the "Filtered" record.
It is possible to update a record from unbounded control. You do this by using an update query. I can't remember the last time I have used a macro in access because using event procedure gives you so much more flexibility it not worth bothering about macro.
Having said that using a bounded textbox would have made you life a lot easier. There would be a lot less codes to worry about. One option might be to nest a bounded form inside the unbounded filter form.
Having said that using a bounded textbox would have made you life a lot easier. There would be a lot less codes to worry about. One option might be to nest a bounded form inside the unbounded filter form.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sb9
An obvious comment first. I AM completely new to Access. With that, I notice a lot of times that dbs that are uploaded by experienced coders such as yourself don't have "relationships" from one table to the next.
Seems that all of that is being done via VB code instead of adding a field with a Lookup in the first table to the associated second table.
Example, in the db you presented dbFilterForm, on the tblComment, fldNameID is not "Linked" through a "lookup" to tblName.
For my own education, is that normal. And if it is, then is it to make the db run faster after it has hundreds upon hundreds of records in it? Or did you simply do that to replicate an example based upon what I was referring to?
Just trying to learn why.
Thanks for the example
An obvious comment first. I AM completely new to Access. With that, I notice a lot of times that dbs that are uploaded by experienced coders such as yourself don't have "relationships" from one table to the next.
Seems that all of that is being done via VB code instead of adding a field with a Lookup in the first table to the associated second table.
Example, in the db you presented dbFilterForm, on the tblComment, fldNameID is not "Linked" through a "lookup" to tblName.
For my own education, is that normal. And if it is, then is it to make the db run faster after it has hundreds upon hundreds of records in it? Or did you simply do that to replicate an example based upon what I was referring to?
Just trying to learn why.
Thanks for the example
Hi wlwebb
Firstly thanks for the compliment. In reality I can hardly call myself a coder, you should see what the other guys on EE can do, its mind blowing. I having been coding for about 3 years and it was pretty daunting to start off. But it gets easier and somehow addictive once you get into it.
Anyway, back to your question;The best way to construct a database is to ensure proper relationship. If this was part of a database that I had constructed as a hold it would definitely have a one-to-many relationship set between tblName and tblComments.
In this particular example I did not create a relationship because it was not required for the purpose of demonstrating the solution to your question. Having said that you can (and I recommend you to) set a relationship between the two tables and the proposed solution will still work with any modification required.
Firstly thanks for the compliment. In reality I can hardly call myself a coder, you should see what the other guys on EE can do, its mind blowing. I having been coding for about 3 years and it was pretty daunting to start off. But it gets easier and somehow addictive once you get into it.
Anyway, back to your question;The best way to construct a database is to ensure proper relationship. If this was part of a database that I had constructed as a hold it would definitely have a one-to-many relationship set between tblName and tblComments.
In this particular example I did not create a relationship because it was not required for the purpose of demonstrating the solution to your question. Having said that you can (and I recommend you to) set a relationship between the two tables and the proposed solution will still work with any modification required.
Hi wlwebb
Regarding my first statement about using an update query to update records from an unbounded form. Check the first example from the following link:
http://www.databasedev.co.uk/forms.html
This is a good website for Access newbies, it explains things in plain English and gives you a lot of examples to download so that you can see what the codes are doing
Regarding my first statement about using an update query to update records from an unbounded form. Check the first example from the following link:
http://www.databasedev.co.uk/forms.html
This is a good website for Access newbies, it explains things in plain English and gives you a lot of examples to download so that you can see what the codes are doing
ASKER
sb9
Sorry for the delay in responding. Sleep deprivation finally set in. Thank you for the insight. I was beginning to wonder if my setting all the "Lookups" in my tables was going to cause me pains down the road. I will review the latest example and try to apply it to my situation.
Again, many thanks.
Sorry for the delay in responding. Sleep deprivation finally set in. Thank you for the insight. I was beginning to wonder if my setting all the "Lookups" in my tables was going to cause me pains down the road. I will review the latest example and try to apply it to my situation.
Again, many thanks.
ASKER
sb9 gave clear instructions and provided great on point examples that were simple to follow and apply to my problem
other than that can you upload a sample database to look at? this will help to know what is going on.