rst find first - somethings changed can't see the forest for the t

[Billp]

Hi,

User enters a name - Fred Jones

The feild on the form is called "Contact Name" and is a combo - which allows
entries which are not in the list.

On after update
Name is split in VBA to
strFirst = Fred
StrLast = Jones


The table behind the combo has the name split into FIRST_NAME and LAST_NAME

and the combo drop down shows a concatenated name "Fred Jones".

Fred Jones is in the drop down.

We select

Now the name is split and we want to check if that name is recorded
strFirst = ParseWord(Me![Contact Name], 1) 'function ParseWord is found
below
strLast = ParseWord(Me![Contact Name], -1)

strsql_1 = "SELECT tblCUSTCONTACTS.[LAST_NAME] " & _
"FROM tblCUSTCONTACTS " & _
"WHERE CustID = " & Me![CustID] 'filter the combo to the customer

Set db = CurrentDb
Set rst = db.OpenRecordset(strsql_1)

strsql_2 = "strLast=""" & rst![LAST_NAME] & """"

If rst.NoMatch Then

'ask if the name is too be added
intpak = MsgBox("Do you wish to add a new contact?" & vbCrLf & _
"Select " & vbCrLf & _
" - YES to open Customer Table to Enter / Delete contacts?"
& vbCrLf & _
" - No To append Name to customer List without Details" &
vbCrLf & _
" - CANCEL Just to accept name as a once off",
vbYesNoCancel, "Update Customer")

Else 'there is a match
'update controls based on Contact Name
etc...............

When it runs I get

Error 3070
Microsft Jet Engine deos not recognise strLast as a valid field name.

All I need is the strLast to be checked against the last_Name feild in the
recorset.

I am looking at it - and I cannot see.

Any help appreciated

Kind Regards

 

[Dirk Goldgar]

Hi,

User enters a name - Fred Jones

The feild on the form is called "Contact Name" and is a combo - which
allows
entries which are not in the list.

On after update
Name is split in VBA to
strFirst = Fred
StrLast = Jones


The table behind the combo has the name split into FIRST_NAME and
LAST_NAME

and the combo drop down shows a concatenated name "Fred Jones".

Fred Jones is in the drop down.

We select

Now the name is split and we want to check if that name is recorded
strFirst = ParseWord(Me![Contact Name], 1) 'function ParseWord is
found
below
strLast = ParseWord(Me![Contact Name], -1)

strsql_1 = "SELECT tblCUSTCONTACTS.[LAST_NAME] " & _
"FROM tblCUSTCONTACTS " & _
"WHERE CustID = " & Me![CustID] 'filter the combo to the
customer

Set db = CurrentDb
Set rst = db.OpenRecordset(strsql_1)

strsql_2 = "strLast=""" & rst![LAST_NAME] & """"

If rst.NoMatch Then

'ask if the name is too be added
intpak = MsgBox("Do you wish to add a new contact?" & vbCrLf & _
"Select " & vbCrLf & _
" - YES to open Customer Table to Enter / Delete contacts?"
& vbCrLf & _
" - No To append Name to customer List without Details" &
vbCrLf & _
" - CANCEL Just to accept name as a once off",
vbYesNoCancel, "Update Customer")

Else 'there is a match
'update controls based on Contact Name
etc...............

When it runs I get

Error 3070
Microsft Jet Engine deos not recognise strLast as a valid field name.

All I need is the strLast to be checked against the last_Name feild in the
recorset.

I am looking at it - and I cannot see.

There are lot of things wrong with your code, I'm afraid, and I think you
must have left some of it out of your post, since I don't see any place
where this erroneous criterion:

strsql_2 = "strLast=""" & rst![LAST_NAME] & """"

.... is used.

If that criterion is to be used in a FindFirst or SQL statement, it would
have to be:

strsql_2 = "[LAST_NAME] = """ & strLast & """"

But there are easier ways to determine whether a name is in the combo box's
list. If the user's entry is in the combo box's list, the control's
ListIndex property will be set to a value >= 0. If the entry is not in the
list, the control's ListIndex property will have a value of -1.

 

[Billp]

Thank you Sir much appreciated.

the > strsql_2 = "strLast=""" & rst![LAST_NAME] & """" came from an example
which seemed to do what I thought would be needing.

What I have is going to be used, first to find if the name doesn't exit then
ask if it is too be added or not - 3 choices.
Then once it is added the combo is refreshed / requeried and that name
selected.
After update the name is checked and then as there is a match becomes an
after update where all information related to that contact fills controls.

It is the only way I could think of doing what was wanted.

I'm all for easy but I'm not using NotInList for the combo because of the 3
choices.


Thank you for your help you have saved me from endless circles of going no
where.

Hi,

User enters a name - Fred Jones

The feild on the form is called "Contact Name" and is a combo - which
allows
entries which are not in the list.

On after update
Name is split in VBA to
strFirst = Fred
StrLast = Jones


The table behind the combo has the name split into FIRST_NAME and
LAST_NAME

and the combo drop down shows a concatenated name "Fred Jones".

Fred Jones is in the drop down.

We select

Now the name is split and we want to check if that name is recorded
strFirst = ParseWord(Me![Contact Name], 1) 'function ParseWord is
found
below
strLast = ParseWord(Me![Contact Name], -1)

strsql_1 = "SELECT tblCUSTCONTACTS.[LAST_NAME] " & _
"FROM tblCUSTCONTACTS " & _
"WHERE CustID = " & Me![CustID] 'filter the combo to the
customer

Set db = CurrentDb
Set rst = db.OpenRecordset(strsql_1)

strsql_2 = "strLast=""" & rst![LAST_NAME] & """"

If rst.NoMatch Then

'ask if the name is too be added
intpak = MsgBox("Do you wish to add a new contact?" & vbCrLf & _
"Select " & vbCrLf & _
" - YES to open Customer Table to Enter / Delete contacts?"
& vbCrLf & _
" - No To append Name to customer List without Details" &
vbCrLf & _
" - CANCEL Just to accept name as a once off",
vbYesNoCancel, "Update Customer")

Else 'there is a match
'update controls based on Contact Name
etc...............

When it runs I get

Error 3070
Microsft Jet Engine deos not recognise strLast as a valid field name.

All I need is the strLast to be checked against the last_Name feild in the
recorset.

I am looking at it - and I cannot see.

There are lot of things wrong with your code, I'm afraid, and I think you
must have left some of it out of your post, since I don't see any place
where this erroneous criterion:

strsql_2 = "strLast=""" & rst![LAST_NAME] & """"

... is used.

If that criterion is to be used in a FindFirst or SQL statement, it would
have to be:

strsql_2 = "[LAST_NAME] = """ & strLast & """"

But there are easier ways to determine whether a name is in the combo box's
list. If the user's entry is in the combo box's list, the control's
ListIndex property will be set to a value >= 0. If the entry is not in the
list, the control's ListIndex property will have a value of -1.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)