Combo box finding wrong record
in [Form]
|
From: KAquestions on 13 May 2010 11:57 Hi all, This is driving me nutty! And i know it's probably something really obvious that i am just not seeing..... I have a form which has a combo box on it. The combo box has 3 columns, First Name, Surname, ClientID (SELECT qryClientDetails.FirstName, qryClientDetails.SurName, qryClientDetails.ClientID FROM qryClientDetails ORDER BY qryClientDetails.FirstName;) When the bound column is 1, it works, but only up to a point. If there are two clients called Robert (Robert A and Robert B) and i want Robert B, when i click on him it always displays the data for Robert A. When the bound column is 3, it always shows the last record in the table, no matter what i have selected. HELP!! TIA, Kirst
From: golfinray on 13 May 2010 14:20 Column numbering starts with zero. So column 3 to the combo is actually column 4, which means nothing. Also, what is your PK? try putting your PK in column zero and just setting the column width of it to 0" and make it the bound column. -- Milton Purdy ACCESS State of Arkansas "KAquestions" wrote: > Hi all, > > This is driving me nutty! And i know it's probably something really obvious > that i am just not seeing..... > > I have a form which has a combo box on it. The combo box has 3 columns, > > First Name, Surname, ClientID > > (SELECT qryClientDetails.FirstName, qryClientDetails.SurName, > qryClientDetails.ClientID FROM qryClientDetails ORDER BY > qryClientDetails.FirstName;) > > When the bound column is 1, it works, but only up to a point. If there are > two clients called Robert (Robert A and Robert B) and i want Robert B, when > i click on him it always displays the data for Robert A. > > When the bound column is 3, it always shows the last record in the table, no > matter what i have selected. > > HELP!! > > TIA, > > Kirst > > > . >
From: BruceM via AccessMonster.com on 13 May 2010 15:11 I'm guessing you have After Update combo box code along the lines of: Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[FirstName] = '" & Me.cboClient & "'" Me.Bookmark = rs.Bookmark If so, the FindFirst is producing the results you are seeing. It is finding the first instance of Robert, no matter who you select. Instead, you could set up the row source query thus: SELECT [ClientID], [Surname] & "," & [FirstName] AS FullName FROM qryClientDetails ORDER BY [Surname], [FirstName] Note that it is a two-column query. I am assuming ClientID is unique for each record, and that it is a number (including autonumber) field. Set the combo box Bound Column to 1, the Column Count to 2, and the Bound Columns to something like 0";2". Your After Update code should be changed to something like: Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[ClientID] = " & Me.cboClient Me.Bookmark = rs.Bookmark This finds the first record in which ClientID matches the combo box selection. Since ClientID is unique, the first matching record is the only one. You may have something like this for the third line of code: If Not rs.EOF Then Me.Bookmark = rs.Bookmark That's OK. I generally do not use "If Not rs.EOF Then", but it does no harm, AFAIK, and may help in circumstances I have not yet encountered. KAquestions wrote: >Hi all, > >This is driving me nutty! And i know it's probably something really obvious >that i am just not seeing..... > >I have a form which has a combo box on it. The combo box has 3 columns, > >First Name, Surname, ClientID > >(SELECT qryClientDetails.FirstName, qryClientDetails.SurName, >qryClientDetails.ClientID FROM qryClientDetails ORDER BY >qryClientDetails.FirstName;) > >When the bound column is 1, it works, but only up to a point. If there are >two clients called Robert (Robert A and Robert B) and i want Robert B, when >i click on him it always displays the data for Robert A. > >When the bound column is 3, it always shows the last record in the table, no >matter what i have selected. > >HELP!! > >TIA, > >Kirst -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201005/1
From: BruceM via AccessMonster.com on 13 May 2010 15:20 The first column is 0 in VBA or in an expression, but on a combo box Property Sheet the first column is 1, so column 3 is literally the third column. I think it would have been better if the same numbering was used everywhere, but there it is. golfinray wrote: >Column numbering starts with zero. So column 3 to the combo is actually >column 4, which means nothing. Also, what is your PK? try putting your PK in >column zero and just setting the column width of it to 0" and make it the >bound column. >> Hi all, >> >[quoted text clipped - 23 lines] >> >> . -- Message posted via http://www.accessmonster.com
From: KAquestions on 14 May 2010 07:48 Hi Bruce, Thanks very much for your response. I have done all that, but keep getting the following error message "Run Time Error 3464 : Data type mismatch in criteria expression" I don't know where i should be looking to fix this. [ClientID] is a PK field, which is Text - it's basically an ID number, but NOT autogenerated. Will this be something to do with it? Also, when i click debug, the line highlighted is: rs.FindFirst "[ClientID] = " & Me.cboClient Any further help would be much appreciated, Kirst "BruceM via AccessMonster.com" <u54429(a)uwe> wrote in message news:a7f4a440ac435(a)uwe... > I'm guessing you have After Update combo box code along the lines of: > > Dim rs As Object > > Set rs = Me.RecordsetClone > rs.FindFirst "[FirstName] = '" & Me.cboClient & "'" > Me.Bookmark = rs.Bookmark > > If so, the FindFirst is producing the results you are seeing. It is > finding > the first instance of Robert, no matter who you select. Instead, you > could > set up the row source query thus: > > SELECT [ClientID], [Surname] & "," & [FirstName] AS FullName > FROM qryClientDetails > ORDER BY [Surname], [FirstName] > > Note that it is a two-column query. I am assuming ClientID is unique for > each record, and that it is a number (including autonumber) field. Set > the > combo box Bound Column to 1, the Column Count to 2, and the Bound Columns > to > something like 0";2". > > Your After Update code should be changed to something like: > > Dim rs As Object > > Set rs = Me.RecordsetClone > rs.FindFirst "[ClientID] = " & Me.cboClient > Me.Bookmark = rs.Bookmark > > This finds the first record in which ClientID matches the combo box > selection. > Since ClientID is unique, the first matching record is the only one. > > You may have something like this for the third line of code: > If Not rs.EOF Then Me.Bookmark = rs.Bookmark > > That's OK. I generally do not use "If Not rs.EOF Then", but it does no > harm, > AFAIK, and may help in circumstances I have not yet encountered. > > KAquestions wrote: >>Hi all, >> >>This is driving me nutty! And i know it's probably something really >>obvious >>that i am just not seeing..... >> >>I have a form which has a combo box on it. The combo box has 3 columns, >> >>First Name, Surname, ClientID >> >>(SELECT qryClientDetails.FirstName, qryClientDetails.SurName, >>qryClientDetails.ClientID FROM qryClientDetails ORDER BY >>qryClientDetails.FirstName;) >> >>When the bound column is 1, it works, but only up to a point. If there are >>two clients called Robert (Robert A and Robert B) and i want Robert B, >>when >>i click on him it always displays the data for Robert A. >> >>When the bound column is 3, it always shows the last record in the table, >>no >>matter what i have selected. >> >>HELP!! >> >>TIA, >> >>Kirst > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201005/1 >
|
Next
|
Last
Pages: 1 2 Prev: How to get one field to update based on a selection from a drop do Next: New Problem - Buttons |