AferInsert event

[Ruth Isaacs]

Hello All

I have a form where, after the user enters a new record, I need the value of
one of the fields (called [websiteID] in that new record to be assigned
using the value one of the other fields (called [postcode] - the user will
have typed this in).

I origially had the following AfterInsert event for the form:

[websiteID].Value = FindAndReplace([postcode], " ", "") & "1"

.... but this did nothing. I tried having a button with the above OnClick
event, and that worked perfectly OK.
I then thought the problem must be that the value of [postcode] had not been
saved to the database at the time the AfterInsert event was firing, so I
amended the AfterInsert event to:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
[websiteID].Value = FindAndReplace([postcode], " ", "") & "1"

.... but now I get a message that the refresh command isn't available now!

So my question is: why is this - and how can I get round it?

Thanks for any help.
Les

 

[strive4peace]

Hi Les,

Rather than using the form AfterInsert event, why not use the
AfterUpdate event of postcode?

try this:
'~~~~~~~~~~~~~~~
if isnull([postcode]) then
me.websiteID = 0
else
'assuming the websiteID is a number...
if IsNumeric([postcode] then
me.websiteID = Replace([postcode], " ", "") & "1"
end if
end if
'~~~~~~~~~~~~~~~

But, if the websiteID is a calculated field, why store it?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Leslie Isaacs]

Hello Crystal

Thanks for your reply.

I understand your suggestion about using the AfterInsert event, but in fact
I need to set the value of [websiteID] ONLY when the record is first added:
if ever the value of [postcode] is subsequently amended the value of
[websiteID] must remain unaffected - so I cannot use the AfterUpdate event.
This is also the reason why I need to store the value of [websiteID].

I'm not sure I understand why you suggest using the IsNumeric function. In
fact the value of [postcode] will typically be mixed alphanumerics and
numerics (field type = text), and so therefore will be the value of
[websiteID].

I'm sure my problem would be sorted if I could just set the value of
[websiteID] immediately after the new record is added - and at no other time
(apart from manually). How can I do that?

Thanks again
Les

Hi Les,

Rather than using the form AfterInsert event, why not use the AfterUpdate
event of postcode?

try this:
'~~~~~~~~~~~~~~~
if isnull([postcode]) then
me.websiteID = 0
else
'assuming the websiteID is a number...
if IsNumeric([postcode] then
me.websiteID = Replace([postcode], " ", "") & "1"
end if
end if
'~~~~~~~~~~~~~~~

But, if the websiteID is a calculated field, why store it?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hello All

I have a form where, after the user enters a new record, I need the value
of
one of the fields (called [websiteID] in that new record to be assigned
using the value one of the other fields (called [postcode] - the user
will
have typed this in).

I origially had the following AfterInsert event for the form:

[websiteID].Value = FindAndReplace([postcode], " ", "") & "1"

... but this did nothing. I tried having a button with the above OnClick
event, and that worked perfectly OK.
I then thought the problem must be that the value of [postcode] had not
been
saved to the database at the time the AfterInsert event was firing, so I
amended the AfterInsert event to:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
[websiteID].Value = FindAndReplace([postcode], " ", "") & "1"

... but now I get a message that the refresh command isn't available now!

So my question is: why is this - and how can I get round it?

Thanks for any help.
Les

 

[strive4peace]

Hi Leslie,

yor're welcome

.... IsNumeric -- ok, I am American <smile> -- just trying to trap for
errors -- but I guess alphas are not problems

If you want the value to go into the same record, use the form
BeforeUpdate event and test for a new record

'~~~~~~~~~~~
If not me.newrecord then
exit sub
end if

'assuming you don't want to assign a code if nothing filled...
if Not isnull([postcode]) then
me.websiteID = Replace([postcode], " ", "") & "1"
end if

'~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hello Crystal

Thanks for your reply.

I understand your suggestion about using the AfterInsert event, but in fact
I need to set the value of [websiteID] ONLY when the record is first added:
if ever the value of [postcode] is subsequently amended the value of
[websiteID] must remain unaffected - so I cannot use the AfterUpdate event.
This is also the reason why I need to store the value of [websiteID].

I'm not sure I understand why you suggest using the IsNumeric function. In
fact the value of [postcode] will typically be mixed alphanumerics and
numerics (field type = text), and so therefore will be the value of
[websiteID].

I'm sure my problem would be sorted if I could just set the value of
[websiteID] immediately after the new record is added - and at no other time
(apart from manually). How can I do that?

Thanks again
Les

Hi Les,

Rather than using the form AfterInsert event, why not use the AfterUpdate
event of postcode?

try this:
'~~~~~~~~~~~~~~~
if isnull([postcode]) then
me.websiteID = 0
else
'assuming the websiteID is a number...
if IsNumeric([postcode] then
me.websiteID = Replace([postcode], " ", "") & "1"
end if
end if
'~~~~~~~~~~~~~~~

But, if the websiteID is a calculated field, why store it?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hello All

I have a form where, after the user enters a new record, I need the value
of
one of the fields (called [websiteID] in that new record to be assigned
using the value one of the other fields (called [postcode] - the user
will
have typed this in).

I origially had the following AfterInsert event for the form:

[websiteID].Value = FindAndReplace([postcode], " ", "") & "1"

... but this did nothing. I tried having a button with the above OnClick
event, and that worked perfectly OK.
I then thought the problem must be that the value of [postcode] had not
been
saved to the database at the time the AfterInsert event was firing, so I
amended the AfterInsert event to:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
[websiteID].Value = FindAndReplace([postcode], " ", "") & "1"

... but now I get a message that the refresh command isn't available now!

So my question is: why is this - and how can I get round it?

Thanks for any help.
Les