A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Combo box finding wrong record



 
 
Thread Tools Display Modes
  #1  
Old May 13th, 2010, 04:57 PM posted to microsoft.public.access.forms
KAquestions
external usenet poster
 
Posts: 20
Default Combo box finding wrong record

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


  #2  
Old May 13th, 2010, 07:20 PM posted to microsoft.public.access.forms
Golfinray
external usenet poster
 
Posts: 1,597
Default Combo box finding wrong record

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


.

  #3  
Old May 13th, 2010, 08:11 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Combo box finding wrong record

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/For...forms/201005/1

  #4  
Old May 13th, 2010, 08:20 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Combo box finding wrong record

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

  #5  
Old May 14th, 2010, 12:48 PM posted to microsoft.public.access.forms
KAquestions
external usenet poster
 
Posts: 20
Default Combo box finding wrong record

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@uwe wrote in message
news:a7f4a440ac435@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/For...forms/201005/1



  #6  
Old May 14th, 2010, 01:41 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Combo box finding wrong record

Quote marks are different for a text field. If ClientID is text you need to
use:

rs.FindFirst "[ClientID] = ' " & Me.cboClient & " ' "

This assumes the bound column of cboClient is ClientID.

If ClientID (or any text field) is in an expression such as the above, and
records may contain an apostrophe (e.g. Joe's Garage) you need a different
approach to the quotes:

rs.FindFirst "[ClientID] = " " " & Me.cboClient & " " " "

I added spaces between the quotes for clarity. If you add spaces the VBA
editor will get rid of them.


KAquestions wrote:
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

I'm guessing you have After Update combo box code along the lines of:

[quoted text clipped - 68 lines]

Kirst


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

  #7  
Old May 14th, 2010, 02:18 PM posted to microsoft.public.access.forms
KAquestions
external usenet poster
 
Posts: 20
Default Combo box finding wrong record

Have tried both!

Now i have no error messages, but no matter what i pick the same record
comes up - the first one in the query?

Kirst

"BruceM via AccessMonster.com" u54429@uwe wrote in message
news:a7fdd05e5dc44@uwe...
Quote marks are different for a text field. If ClientID is text you need
to
use:

rs.FindFirst "[ClientID] = ' " & Me.cboClient & " ' "

This assumes the bound column of cboClient is ClientID.

If ClientID (or any text field) is in an expression such as the above, and
records may contain an apostrophe (e.g. Joe's Garage) you need a different
approach to the quotes:

rs.FindFirst "[ClientID] = " " " & Me.cboClient & " " " "

I added spaces between the quotes for clarity. If you add spaces the VBA
editor will get rid of them.


KAquestions wrote:
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

I'm guessing you have After Update combo box code along the lines of:

[quoted text clipped - 68 lines]

Kirst


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1



  #8  
Old May 14th, 2010, 02:34 PM posted to microsoft.public.access.forms
KAquestions
external usenet poster
 
Posts: 20
Default Combo box finding wrong record

I have done it again and got it working, I must have had a typo

I really appreciate all your help, thanks,

Kirst

"KAquestions" (no_spam) wrote in message
...
Have tried both!

Now i have no error messages, but no matter what i pick the same record
comes up - the first one in the query?

Kirst

"BruceM via AccessMonster.com" u54429@uwe wrote in message
news:a7fdd05e5dc44@uwe...
Quote marks are different for a text field. If ClientID is text you need
to
use:

rs.FindFirst "[ClientID] = ' " & Me.cboClient & " ' "

This assumes the bound column of cboClient is ClientID.

If ClientID (or any text field) is in an expression such as the above,
and
records may contain an apostrophe (e.g. Joe's Garage) you need a
different
approach to the quotes:

rs.FindFirst "[ClientID] = " " " & Me.cboClient & " " " "

I added spaces between the quotes for clarity. If you add spaces the VBA
editor will get rid of them.


KAquestions wrote:
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

I'm guessing you have After Update combo box code along the lines of:

[quoted text clipped - 68 lines]

Kirst


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.