Combo box in form error
in [Getting Started]
|
From: rpbsr on 2 Jul 2008 13:26 I am trying to add a combo box to a form based on tblMembers that will allow a member to be selected from a dropdown list along with data in related fields. A suggestion I got was to setup the combo box (cboFindMember) witih the following properties: RowSource: SELECT MemberID, LastName, FirstName FROM tblMembers ORDER BY LastName, FirstName; BoundColum: 1 ColumnCount: 3 ColumnWidths: 0cm;2.5cm;2.5cm ListWidth: 5cm LimitToList: Yes AutoExpand: Yes In the combo box's AfterUpdate event procedure put the following code: Dim rst As Object Set rst = Me.Recordset.Clone With rst .FindFirst "MemberID = " & cboFindMember If Not .NoMatch Then Me.Bookmark = .Bookmark End If End With I wasn't told what the control source should be, so I chose "LastName". Problem: When I click on the control I get "The value you entered isn't valid for this field. For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits". Also, it was recommended to consider a refinement to the code: Me.cboFindMember = Me.MemberID Where in the code would this statement be placed? Thanks.
From: Beetle on 2 Jul 2008 15:27 The control source of the combo box should be MemberID, not LastName. The other line of code should go in the forms Current event. In the case where a user navigates to a different record by using one of the form navigation buttons, instead of the combo box, it would keep the combo box in synch with the form. -- _________ Sean Bailey "rpbsr" wrote: > I am trying to add a combo box to a form based on tblMembers that will allow > a member to be selected from a dropdown list along with data in related > fields. A suggestion I got was to setup the combo box (cboFindMember) witih > the following properties: > > RowSource: SELECT MemberID, LastName, FirstName FROM tblMembers ORDER BY > LastName, FirstName; > > BoundColum: 1 > ColumnCount: 3 > ColumnWidths: 0cm;2.5cm;2.5cm > ListWidth: 5cm > LimitToList: Yes > AutoExpand: Yes > > In the combo box's AfterUpdate event procedure put the following code: > > Dim rst As Object > > Set rst = Me.Recordset.Clone > > With rst > .FindFirst "MemberID = " & cboFindMember > If Not .NoMatch Then > Me.Bookmark = .Bookmark > End If > End With > > I wasn't told what the control source should be, so I chose "LastName". > Problem: When I click on the control I get "The value you entered isn't > valid for this field. For example, you may have entered text in a numeric > field or a number that is larger than the FieldSize setting permits". > > Also, it was recommended to consider a refinement to the code: > Me.cboFindMember = Me.MemberID > Where in the code would this statement be placed? > > Thanks.
From: Klatuu on 2 Jul 2008 15:51 Point of Order, Mr. Baily. A Search Combo should not be a bound control. The problem is, when you try to search for a record in the recordset with a bound combo, you actually change the value of the current record. And, if the bound field is the table's primary key, you will get a key violation error. In fact, it is not necessary nor really even desirable (IMHO) to use a control for the record's primary key field. It just needs to be in the form's recordset. -- Dave Hargis, Microsoft Access MVP "Beetle" wrote: > The control source of the combo box should be MemberID, not LastName. > > The other line of code should go in the forms Current event. In the case > where a user navigates to a different record by using one of the form > navigation buttons, instead of the combo box, it would keep the combo > box in synch with the form. > -- > _________ > > Sean Bailey > > > "rpbsr" wrote: > > > I am trying to add a combo box to a form based on tblMembers that will allow > > a member to be selected from a dropdown list along with data in related > > fields. A suggestion I got was to setup the combo box (cboFindMember) witih > > the following properties: > > > > RowSource: SELECT MemberID, LastName, FirstName FROM tblMembers ORDER BY > > LastName, FirstName; > > > > BoundColum: 1 > > ColumnCount: 3 > > ColumnWidths: 0cm;2.5cm;2.5cm > > ListWidth: 5cm > > LimitToList: Yes > > AutoExpand: Yes > > > > In the combo box's AfterUpdate event procedure put the following code: > > > > Dim rst As Object > > > > Set rst = Me.Recordset.Clone > > > > With rst > > .FindFirst "MemberID = " & cboFindMember > > If Not .NoMatch Then > > Me.Bookmark = .Bookmark > > End If > > End With > > > > I wasn't told what the control source should be, so I chose "LastName". > > Problem: When I click on the control I get "The value you entered isn't > > valid for this field. For example, you may have entered text in a numeric > > field or a number that is larger than the FieldSize setting permits". > > > > Also, it was recommended to consider a refinement to the code: > > Me.cboFindMember = Me.MemberID > > Where in the code would this statement be placed? > > > > Thanks.
From: Beetle on 2 Jul 2008 17:09 <removes egg from face> Duh! I knew that. I just wasn't thinking clearly apparently. Thanks for pointing out my mistake. -- _________ Sean Bailey "Klatuu" wrote: > Point of Order, Mr. Baily. > A Search Combo should not be a bound control. The problem is, when you try > to search for a record in the recordset with a bound combo, you actually > change the value of the current record. And, if the bound field is the > table's primary key, you will get a key violation error. > > In fact, it is not necessary nor really even desirable (IMHO) to use a > control for the record's primary key field. It just needs to be in the > form's recordset. > > -- > Dave Hargis, Microsoft Access MVP > > > "Beetle" wrote: > > > The control source of the combo box should be MemberID, not LastName. > > > > The other line of code should go in the forms Current event. In the case > > where a user navigates to a different record by using one of the form > > navigation buttons, instead of the combo box, it would keep the combo > > box in synch with the form. > > -- > > _________ > > > > Sean Bailey > > > > > > "rpbsr" wrote: > > > > > I am trying to add a combo box to a form based on tblMembers that will allow > > > a member to be selected from a dropdown list along with data in related > > > fields. A suggestion I got was to setup the combo box (cboFindMember) witih > > > the following properties: > > > > > > RowSource: SELECT MemberID, LastName, FirstName FROM tblMembers ORDER BY > > > LastName, FirstName; > > > > > > BoundColum: 1 > > > ColumnCount: 3 > > > ColumnWidths: 0cm;2.5cm;2.5cm > > > ListWidth: 5cm > > > LimitToList: Yes > > > AutoExpand: Yes > > > > > > In the combo box's AfterUpdate event procedure put the following code: > > > > > > Dim rst As Object > > > > > > Set rst = Me.Recordset.Clone > > > > > > With rst > > > .FindFirst "MemberID = " & cboFindMember > > > If Not .NoMatch Then > > > Me.Bookmark = .Bookmark > > > End If > > > End With > > > > > > I wasn't told what the control source should be, so I chose "LastName". > > > Problem: When I click on the control I get "The value you entered isn't > > > valid for this field. For example, you may have entered text in a numeric > > > field or a number that is larger than the FieldSize setting permits". > > > > > > Also, it was recommended to consider a refinement to the code: > > > Me.cboFindMember = Me.MemberID > > > Where in the code would this statement be placed? > > > > > > Thanks.
From: Klatuu on 2 Jul 2008 17:14 No problem. But then, I neber make mistrakes :) -- Dave Hargis, Microsoft Access MVP "Beetle" wrote: > <removes egg from face> > > Duh! I knew that. I just wasn't thinking clearly apparently. Thanks for > pointing out my mistake. > -- > _________ > > Sean Bailey > > > "Klatuu" wrote: > > > Point of Order, Mr. Baily. > > A Search Combo should not be a bound control. The problem is, when you try > > to search for a record in the recordset with a bound combo, you actually > > change the value of the current record. And, if the bound field is the > > table's primary key, you will get a key violation error. > > > > In fact, it is not necessary nor really even desirable (IMHO) to use a > > control for the record's primary key field. It just needs to be in the > > form's recordset. > > > > -- > > Dave Hargis, Microsoft Access MVP > > > > > > "Beetle" wrote: > > > > > The control source of the combo box should be MemberID, not LastName. > > > > > > The other line of code should go in the forms Current event. In the case > > > where a user navigates to a different record by using one of the form > > > navigation buttons, instead of the combo box, it would keep the combo > > > box in synch with the form. > > > -- > > > _________ > > > > > > Sean Bailey > > > > > > > > > "rpbsr" wrote: > > > > > > > I am trying to add a combo box to a form based on tblMembers that will allow > > > > a member to be selected from a dropdown list along with data in related > > > > fields. A suggestion I got was to setup the combo box (cboFindMember) witih > > > > the following properties: > > > > > > > > RowSource: SELECT MemberID, LastName, FirstName FROM tblMembers ORDER BY > > > > LastName, FirstName; > > > > > > > > BoundColum: 1 > > > > ColumnCount: 3 > > > > ColumnWidths: 0cm;2.5cm;2.5cm > > > > ListWidth: 5cm > > > > LimitToList: Yes > > > > AutoExpand: Yes > > > > > > > > In the combo box's AfterUpdate event procedure put the following code: > > > > > > > > Dim rst As Object > > > > > > > > Set rst = Me.Recordset.Clone > > > > > > > > With rst > > > > .FindFirst "MemberID = " & cboFindMember > > > > If Not .NoMatch Then > > > > Me.Bookmark = .Bookmark > > > > End If > > > > End With > > > > > > > > I wasn't told what the control source should be, so I chose "LastName". > > > > Problem: When I click on the control I get "The value you entered isn't > > > > valid for this field. For example, you may have entered text in a numeric > > > > field or a number that is larger than the FieldSize setting permits". > > > > > > > > Also, it was recommended to consider a refinement to the code: > > > > Me.cboFindMember = Me.MemberID > > > > Where in the code would this statement be placed? > > > > > > > > Thanks.
|
Next
|
Last
Pages: 1 2 Prev: What is an msl file? Can I convert it to Excel? Next: Autonumber for Report |