Minggu, 10 Juli 2011

[MS_AccessPros] Re: Synchronized combo boxes on forms

 

Patrick, thanks for your help. I made the code changes you suggested. I am now able to select a customer without an error, but there is nothing in the product dropdown. Here is how the code looks now. I put a revised zip file in my "PTS Time Tool" folder.

Me.cboProduct.RowSource = "SELECT Product" _
& " FROM TblProduct" _
& " WHERE Customer = """ & Me.cboCustomer & """" _
& " ORDER BY Product;"
Me.cboProduct.Requery
Me.cboProduct = Me.cboProduct.ItemData(0)

--- In MS_Access_Professionals@yahoogroups.com, "patrickawood" <pwood57@...> wrote:
>
> Hi Bradley,
>
> It is very late so I have not looked at your file but you need to add
> Me.cboProduct.Requery
> right before
> Me.cboProduct. = Me.cboProduct.ItemData(0)
>
> If the cboCustomer's first column is a Number your code looks OK but if it is a name or Text data type then you need to add some quotes and change your code to:
> Me.cboProduct.RowSource = "SELECT Product" _
> & " FROM TblProduct" _
> & " WHERE Customer = """ & Me.cboCustomer & """" _
> & " ORDER BY Product;"
>
> The extra quotes make sure that if the Customer is O'Brian then the ' will not cause Access to think it marks the end of our text and cause an error.
>
> Kind Regards,
> Patrick Wood
> http://accesstips.wordpress.com
>
> --- In MS_Access_Professionals@yahoogroups.com, "Bradley" <bradgriffis@> wrote:
> >
> > Hello, I am creating a time tracking database for my department at work. I use Access 2007 and my experience level is beginner. I am having trouble synchronizing the customer and product combo boxes on my main data entry form. I want the selection in the customer combo to limit the choices in the product combo. When adding a new record, selecting a customer from the dropdown gives the error message: "The value you entered isn't valid for this field."
> >
> > I am using the suggestions and code from this website:
> > http://msdn.microsoft.com/en-us/library/bb404901%28v=office.12%29.aspx
> >
> > I put a zipped copy of my database in a folder named "PTS Time Tool" in the "Assistance Needed" folder. The file is named "TimeTool_2"
> >
> > To see the error, pull up the database and click the "Add Time Records" button. Login with username=Griffis and password=staff1. This will bring up the data entry screen. When adding a new record, selecting a customer gives the error message.
> >
> > The code for the customer combo "after update" event is below. The error occurs in the last line. I am not familiar with VB and just copy and paste what I need from other sources and do my best to figure it out. Keep in mind that my test data has ten customers, but only the first five need products. Customers six through ten do not need to show a product in the dropdown.
> >
> > Private Sub cboCustomer_AfterUpdate()
> > Me.cboProduct.RowSource = "SELECT Product FROM" & _
> > " TblProduct WHERE Customer = " & _
> > Me.cboCustomer & _
> > " ORDER BY Product"
> >
> > Me.cboProduct = Me.cboProduct.ItemData(0)
> >
> >
> >
> > Thanks,
> > Brad
> >
>

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar