I'm going nuts over this one.

IF I have the same last name the system will bring back the first record
with the last name that I chose no matter where in teh combobox it resides.
CustID is the key field but I don't want the user to i, but I need the
system to grab it to stuff the fields on the form. I have tried many ways but
this is what I have now
and my Afterupdate Property doe's not like a SQL statement with the CustID
in it.

The Afterupdate works but does no t bring back the correct record.

Here is what I have.

I have in the table
SELECT Prospect.CUSTID, Prospect.CUSTLAST, Prospect.CUSTFIRST,
Prospect.CUSTMID, Prospect.CUSTADD1, Prospect.CUSTCITY, Prospect.CUSTSTATE,
Prospect.CUSTZIP, Prospect.CUSTPHONE FROM Prospect ORDER BY
Prospect.CUSTLAST;

Combo Box Specs
0";1.3125";0.75";0.75";1";0.75";0.75";0.75";1"

Bound Column = 2

AfterUpdate
strNewRecord = "SELECT * FROM Prospect WHERE CustLast = '" &
Me!cmbProspect.Value & "'"

Thanks
--
Andre

<(E-Mail Removed)> wrote:

See the note in line!

> I'm going nuts over this one.
>
> IF I have the same last name the system will bring back the first
> record with the last name that I chose no matter where in teh combobox
> it resides. CustID is the key field but I don't want the user to i,
> but I need the system to grab it to stuff the fields on the form. I
> have tried many ways but this is what I have now
> and my Afterupdate Property doe's not like a SQL statement with the
> CustID in it.
>
> The Afterupdate works but does no t bring back the correct record.
>
> Here is what I have.
>
> I have in the table
> SELECT Prospect.CUSTID, Prospect.CUSTLAST, Prospect.CUSTFIRST,
> Prospect.CUSTMID, Prospect.CUSTADD1, Prospect.CUSTCITY,
> Prospect.CUSTSTATE, Prospect.CUSTZIP, Prospect.CUSTPHONE FROM Prospect
> ORDER BY Prospect.CUSTLAST;
>
> Combo Box Specs
> 0";1.3125";0.75";0.75";1";0.75";0.75";0.75";1"
>
> Bound Column = 2
>
> AfterUpdate
> strNewRecord = "SELECT * FROM Prospect WHERE CustLast = '" &
> Me!cmbProspect.Value & "'"

Try Me.cmbProspect.Column(1)

>
> Thanks

HTH

--
RuralGuy

Please reply to the newsgroup so all may benefit.

Hi Bas,

I've enjoyed reading your posts and also like your web site information.

With this last post I have a couple of questions.

1) Is your clock set correctly?
2) Can't you just use the RecordSetClone without any initialization?

ie:

Me.RecordSetClone.FindFirst whateverexpressionwiththecomboboxvalue

Rather than:

> dim rs as recordset
> set rs=me.recordsetclone
> rs.findfirst whateverexpressionwiththecomboboxvalue
> .
> .
> .
> set rs=nothing

Just thought I'd ask.

--
RuralGuy

Please reply to the newsgroup so all may benefit.

If you want to use the combobox for navigation only, and you can have it
display the same source as the form, in the same order (which I'd
prefer), you can put this in the Click event of the combobox:

docmd.gotorecord,,acgoto,listboxcontrolname.listindex+1

Otherwise

dim rs as recordset
set rs=me.recordsetclone
rs.findfirst whateverexpressionwiththecomboboxvalue ' so why do we use
spaces in English?
me.bookmark=rs.bookmark
set rs=nothing

Andre wrote:
> I'm going nuts over this one.
>
> IF I have the same last name the system will bring back the first record
> with the last name that I chose no matter where in teh combobox it resides.
> CustID is the key field but I don't want the user to i, but I need the
> system to grab it to stuff the fields on the form. I have tried many ways but
> this is what I have now
> and my Afterupdate Property doe's not like a SQL statement with the CustID
> in it.
>
> The Afterupdate works but does no t bring back the correct record.
>
> Here is what I have.
>
> I have in the table
> SELECT Prospect.CUSTID, Prospect.CUSTLAST, Prospect.CUSTFIRST,
> Prospect.CUSTMID, Prospect.CUSTADD1, Prospect.CUSTCITY, Prospect.CUSTSTATE,
> Prospect.CUSTZIP, Prospect.CUSTPHONE FROM Prospect ORDER BY
> Prospect.CUSTLAST;
>
> Combo Box Specs
> 0";1.3125";0.75";0.75";1";0.75";0.75";0.75";1"
>
> Bound Column = 2
>
> AfterUpdate
> strNewRecord = "SELECT * FROM Prospect WHERE CustLast = '" &
> Me!cmbProspect.Value & "'"
>
> Thanks

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

RuralGuy,

Thanks for the Insight.

This was the solution

strNewRecord = "SELECT * FROM Prospect WHERE CustID = " &
Me!cmbProspect.Column(0) & ""


--
Andre


"RuralGuy" wrote:

> <(E-Mail Removed)> wrote:
>
> See the note in line!
>
> > I'm going nuts over this one.
> >
> > IF I have the same last name the system will bring back the first
> > record with the last name that I chose no matter where in teh combobox
> > it resides. CustID is the key field but I don't want the user to i,
> > but I need the system to grab it to stuff the fields on the form. I
> > have tried many ways but this is what I have now
> > and my Afterupdate Property doe's not like a SQL statement with the
> > CustID in it.
> >
> > The Afterupdate works but does no t bring back the correct record.
> >
> > Here is what I have.
> >
> > I have in the table
> > SELECT Prospect.CUSTID, Prospect.CUSTLAST, Prospect.CUSTFIRST,
> > Prospect.CUSTMID, Prospect.CUSTADD1, Prospect.CUSTCITY,
> > Prospect.CUSTSTATE, Prospect.CUSTZIP, Prospect.CUSTPHONE FROM Prospect
> > ORDER BY Prospect.CUSTLAST;
> >
> > Combo Box Specs
> > 0";1.3125";0.75";0.75";1";0.75";0.75";0.75";1"
> >
> > Bound Column = 2
> >
> > AfterUpdate
> > strNewRecord = "SELECT * FROM Prospect WHERE CustLast = '" &
> > Me!cmbProspect.Value & "'"
>
> Try Me.cmbProspect.Column(1)
>
> >
> > Thanks
>
> HTH
>
> --
> RuralGuy
>
> Please reply to the newsgroup so all may benefit.
>

Bas,

Thanks for the Insight.

This was the solution

strNewRecord = "SELECT * FROM Prospect WHERE CustID = " &
Me!cmbProspect.Column(0) & ""


--
Andre


"Bas Cost Budde" wrote:

> If you want to use the combobox for navigation only, and you can have it
> display the same source as the form, in the same order (which I'd
> prefer), you can put this in the Click event of the combobox:
>
> docmd.gotorecord,,acgoto,listboxcontrolname.listindex+1
>
> Otherwise
>
> dim rs as recordset
> set rs=me.recordsetclone
> rs.findfirst whateverexpressionwiththecomboboxvalue ' so why do we use
> spaces in English?
> me.bookmark=rs.bookmark
> set rs=nothing
>
> Andre wrote:
> > I'm going nuts over this one.
> >
> > IF I have the same last name the system will bring back the first record
> > with the last name that I chose no matter where in teh combobox it resides.
> > CustID is the key field but I don't want the user to i, but I need the
> > system to grab it to stuff the fields on the form. I have tried many ways but
> > this is what I have now
> > and my Afterupdate Property doe's not like a SQL statement with the CustID
> > in it.
> >
> > The Afterupdate works but does no t bring back the correct record.
> >
> > Here is what I have.
> >
> > I have in the table
> > SELECT Prospect.CUSTID, Prospect.CUSTLAST, Prospect.CUSTFIRST,
> > Prospect.CUSTMID, Prospect.CUSTADD1, Prospect.CUSTCITY, Prospect.CUSTSTATE,
> > Prospect.CUSTZIP, Prospect.CUSTPHONE FROM Prospect ORDER BY
> > Prospect.CUSTLAST;
> >
> > Combo Box Specs
> > 0";1.3125";0.75";0.75";1";0.75";0.75";0.75";1"
> >
> > Bound Column = 2
> >
> > AfterUpdate
> > strNewRecord = "SELECT * FROM Prospect WHERE CustLast = '" &
> > Me!cmbProspect.Value & "'"
> >
> > Thanks
>
> --
> Bas Cost Budde, Holland
> http://www.heuveltop.nl/BasCB/msac_index.html
> For human replies, replace the queue with a tea
>
>

<(E-Mail Removed)> wrote:

> RuralGuy,
>
> Thanks for the Insight.
>
> This was the solution
>
> strNewRecord = "SELECT * FROM Prospect WHERE CustID = " &
> Me!cmbProspect.Column(0) & ""

You're welcome and thanks for posting back with your success!

--
RuralGuy

Please reply to the newsgroup so all may benefit.

> I've enjoyed reading your posts and also like your web site information.
>
> With this last post I have a couple of questions.
>
> 1) Is your clock set correctly?

I doubt it :-) [snip complicated story about nntp] but it shouldn't be
far off. Your question raises the doubt that slept. What time is it,
then? Local time, just in case some intermediate machine ruins it, is
1851 (MEST or GMT+2)

> 2) Can't you just use the RecordSetClone without any initialization?

Ah, I've never tried. [tries] Why, yes. That is way shorter, and easier
on the eye.

With Me.RecordsetClone
.FindFirst "keyfield='" & ctlKeyChooser & "'"
If Not .NoMatch Then' of course you don't have to test this!
Me.Bookmark = .Bookmark
End If
End With

> Just thought I'd ask.

Here we have the real value of human contact over Help files.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea