Command Button Question/Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good Day Everyone..

I have a form which is used to write all of my data to the main table. This
all happens when the user clicks the 'Add' button.

Unfortunately, I need to have another function happen at the same time. I
need for the database to check the new record for a piece of data linked in
another table. (Ex. I say that associate xx is out for the day. As I write
the entry for associate xx to the table, I need to see if xx is a member of
another other table. If so then I need to run a macro which fires off an
e-mail to the selected users advising of the absence.)

I have built the macro as that was pretty darn simple but I am stuck on the
VB needed to make the command button do everything I want. If at all
possible.

Any help is greatly appreciated as this is my last problem with this newly
created database.

Thanks,
Jim
 
Good Day Everyone..

I have a form which is used to write all of my data to the main table. This
all happens when the user clicks the 'Add' button.

Unfortunately, I need to have another function happen at the same time. I
need for the database to check the new record for a piece of data linked in
another table. (Ex. I say that associate xx is out for the day. As I write
the entry for associate xx to the table, I need to see if xx is a member of
another other table. If so then I need to run a macro which fires off an
e-mail to the selected users advising of the absence.)

I have built the macro as that was pretty darn simple but I am stuck on the
VB needed to make the command button do everything I want. If at all
possible.

Any help is greatly appreciated as this is my last problem with this newly
created database.

Thanks,
Jim
Click to expand...

If you simply wish to see if XX is in another table, then:

If DCount("*","OtherTableName","[FieldName] = """ & Me![ControlName] &
"""") > 0 then
MsgBox "Yup! He's entered in the other table"
Else
MsgBox "Nope!, Not entered in other table."
End If

Where [FieldName] is the name of the field in the other table that
contains XX, and [ControlName] is the name of the control on this form
that you entered XX into.
 
Thank you for the help but I still have an issue. This works but doesn't.
Here is the VB with my names.

Private Sub Add_Button_Click()
On Error GoTo Err_Add_Button_Click

DoCmd.GoToRecord , , acNewRec

If DCount("*", "Switchboard List", "[Associate Name] = """ & Me![Name] &
"""") > 0 Then
MsgBox "Yup! He's entered in the other table"
Else
MsgBox "Nope!, Not entered in other table."
End If

Exit_Add_Button_Click:
Exit Sub

Err_Add_Button_Click:
MsgBox Err.Description
Resume Exit_Add_Button_Click

End Sub

Unfortunately, I tested w/ two agent names on the other list and it said
they were not on the other list. *sigh*

Did I make an error when changing something you provided?

Jim
--
"Baliff. Whack him in the pee-pee!"
"How come his is so much bigger then yours?"
"I am not even suppose to be here today!"


fredg said:
Good Day Everyone..

I have a form which is used to write all of my data to the main table. This
all happens when the user clicks the 'Add' button.

Unfortunately, I need to have another function happen at the same time. I
need for the database to check the new record for a piece of data linked in
another table. (Ex. I say that associate xx is out for the day. As I write
the entry for associate xx to the table, I need to see if xx is a member of
another other table. If so then I need to run a macro which fires off an
e-mail to the selected users advising of the absence.)

I have built the macro as that was pretty darn simple but I am stuck on the
VB needed to make the command button do everything I want. If at all
possible.

Any help is greatly appreciated as this is my last problem with this newly
created database.

Thanks,
Jim
Click to expand...

If you simply wish to see if XX is in another table, then:

If DCount("*","OtherTableName","[FieldName] = """ & Me![ControlName] &
"""") > 0 then
MsgBox "Yup! He's entered in the other table"
Else
MsgBox "Nope!, Not entered in other table."
End If

Where [FieldName] is the name of the field in the other table that
contains XX, and [ControlName] is the name of the control on this form
that you entered XX into.
Click to expand...
 
Thank you for the help but I still have an issue. This works but doesn't.
Here is the VB with my names.

Private Sub Add_Button_Click()
On Error GoTo Err_Add_Button_Click

DoCmd.GoToRecord , , acNewRec

If DCount("*", "Switchboard List", "[Associate Name] = """ & Me![Name] &
"""") > 0 Then
MsgBox "Yup! He's entered in the other table"
Else
MsgBox "Nope!, Not entered in other table."
End If

Exit_Add_Button_Click:
Exit Sub

Err_Add_Button_Click:
MsgBox Err.Description
Resume Exit_Add_Button_Click

End Sub

Unfortunately, I tested w/ two agent names on the other list and it said
they were not on the other list. *sigh*

Did I make an error when changing something you provided?

Jim
Click to expand...

1) ) What is the purpose of going to a new record, and running this
code in the same event?
You have not entered anything yet in the new record and this code
runs. So your [Name] control is empty.
You should check for the existence of the XX record in that [Name]
controls BeforeUpdate (or perhaps AfterUpdate) event, not in this
command button event.

2) Name is an Access reserved word and should not be used as a field
name.
In your instance Me![Name] can be seen by Access as referring to the
name of the form. Change Name to something else, perhaps "txtName".

3) Is the [Associate Name] datatype Text? If so then the DCount should
work if the control on the form has a value. See #1 and #2 above.

If, however, [Associate name] is actually a Number datatype, then use:

If DCount("*", "Switchboard List", "[Associate Name] = " & Me![Name])
Then
etc....
 
Fred..

I did not realize that it was reserved. I made the change to the field so
that it is now Associate Name. As suggested, I put this on the AfterUpdate
event opposed to the Add Button event and it works like a charm. I had it
working with a Macro to fire off an e-mail but that may take some time to
play with. I know there may be a time when the user does not have Outlook
opened and I don't want to delay the delivery of the e-mail event. I am open
to any suggestions you have here.

Thank you for taking the time to help me out here. This has wrapped up the
problems I had replicating this in Access from FoxPro.

Thanks,
Jim
--
"Baliff. Whack him in the pee-pee!"
"How come his is so much bigger then yours?"
"I am not even suppose to be here today!"


fredg said:
Thank you for the help but I still have an issue. This works but doesn't.
Here is the VB with my names.

Private Sub Add_Button_Click()
On Error GoTo Err_Add_Button_Click

DoCmd.GoToRecord , , acNewRec

If DCount("*", "Switchboard List", "[Associate Name] = """ & Me![Name] &
"""") > 0 Then
MsgBox "Yup! He's entered in the other table"
Else
MsgBox "Nope!, Not entered in other table."
End If

Exit_Add_Button_Click:
Exit Sub

Err_Add_Button_Click:
MsgBox Err.Description
Resume Exit_Add_Button_Click

End Sub

Unfortunately, I tested w/ two agent names on the other list and it said
they were not on the other list. *sigh*

Did I make an error when changing something you provided?

Jim
Click to expand...

1) ) What is the purpose of going to a new record, and running this
code in the same event?
You have not entered anything yet in the new record and this code
runs. So your [Name] control is empty.
You should check for the existence of the XX record in that [Name]
controls BeforeUpdate (or perhaps AfterUpdate) event, not in this
command button event.

2) Name is an Access reserved word and should not be used as a field
name.
In your instance Me![Name] can be seen by Access as referring to the
name of the form. Change Name to something else, perhaps "txtName".

3) Is the [Associate Name] datatype Text? If so then the DCount should
work if the control on the form has a value. See #1 and #2 above.

If, however, [Associate name] is actually a Number datatype, then use:

If DCount("*", "Switchboard List", "[Associate Name] = " & Me![Name])
Then
etc....
Click to expand...
 
Back
Top