Use If / Then to call a module

[Opal]

This is probably because I have trouble getting my head around
Nulls...

I have a text box and if there is a value (not null) I want to call a
module to email a report to a user. If there is no value (null) I
want it to do nothing.

I have tried:

If Len([txtAreaResp] & "") <> 0 Then
Assign
End If

Assign is the module I want to call to email the report to the user.
I know I have this wrong....Should I be using Nz?

Any help would be appreciated.

 

[Douglas J. Steele]

That looks appropriate. What's the problem you're encountering?

 

[Opal]

That looks appropriate. What's the problem you're encountering?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

This is probably because I have trouble getting my head around
Nulls...

I have a text box and if there is a value (not null) I want to call a
module to email a report to a user.  If there is no value (null) I
want it to do nothing.

I have tried:

If Len([txtAreaResp] & "") <> 0 Then
   Assign
   End If

Assign is the module I want to call to email the report to the user.
I know I have this wrong....Should I be using Nz?

Any help would be appreciated.- Hide quoted text -

- Show quoted text -

Hi Duane,

It skips over the "Assign" line and does not go to the module when
there is a value in the text box.

I even tried: If Not IsNull(Me.txtAreaResp) Then

and I get the same results.

 

[Douglas J. Steele]

Are you sure that the If code is actually been run? Have you tried
single-stepping through the code?

Go into the VB Editor and click in the margin to the left of the If
statement. A large dot should appear in the margin, and the line of code
should be highlighted. Now, do whatever you do to make that code run (i.e.:
open the form and click on the button, enter data and move from away from
the field, etc.) If the code's running, execution will break and you'll be
taken to that line of code just before it executes. When it breaks, hold
your mouse cursor over [txtAreaResp] to see what Access thinks the value is
(or you can go to the Debug window, type ?txtAreaResp and hit Enter). Hit F8
to go to the next line of code: where do you end up?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This is probably because I have trouble getting my head around
Nulls...

I have a text box and if there is a value (not null) I want to call a
module to email a report to a user. If there is no value (null) I
want it to do nothing.

I have tried:

If Len([txtAreaResp] & "") <> 0 Then
Assign
End If

Assign is the module I want to call to email the report to the user.
I know I have this wrong....Should I be using Nz?

Any help would be appreciated.

Hi Duane,

It skips over the "Assign" line and does not go to the module when
there is a value in the text box.

I even tried: If Not IsNull(Me.txtAreaResp) Then

and I get the same results.

 

[Opal]

Are you sure that the If code is actually been run? Have you tried
single-stepping through the code?

Go into the VB Editor and click in the margin to the left of the If
statement. A large dot should appear in the margin, and the line of code
should be highlighted. Now, do whatever you do to make that code run (i.e.:
open the form and click on the button, enter data and move from away from
the field, etc.) If the code's running, execution will break and you'll be
taken to that line of code just before it executes. When it breaks, hold
your mouse cursor over [txtAreaResp] to see what Access thinks the value is
(or you can go to the Debug window, type ?txtAreaResp and hit Enter). Hit F8
to go to the next line of code: where do you end up?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

This is probably because I have trouble getting my head around
Nulls...
I have a text box and if there is a value (not null) I want to call a
module to email a report to a user. If there is no value (null) I
want it to do nothing.
I have tried:
If Len([txtAreaResp] & "") <> 0 Then
Assign
End If
Assign is the module I want to call to email the report to the user.
I know I have this wrong....Should I be using Nz?
Any help would be appreciated.

Hi Duane,

It skips over the "Assign" line and does not go to the module when
there is a value in the text box.

I even tried:  If Not IsNull(Me.txtAreaResp) Then

and I get the same results.- Hide quoted text -

- Show quoted text -

Thanks Duane....I am still new at debugging code..... I see what is
happening, but I am not
certain as to how to correct it.

Here is what I am trying to do. The user is entering data from an
unbound form into a table.
Once the data is submitted to the table I would like the database to
email another user whose
area (as noted in the txtAreaResp) appears automatically. I am using
a DEnd code in the
txtAreaResp textbox to identify the last updated area on the table so
as to call the email
module.

Now, I am wondering if I should work from a bound form to do
this...... The txtAreaResp
is coming up "Null" even after the re-query.

 

[Opal]

Are you sure that the If code is actually been run? Have you tried
single-stepping through the code?

Go into the VB Editor and click in the margin to the left of the If
statement. A large dot should appear in the margin, and the line of code
should be highlighted. Now, do whatever you do to make that code run (i.e.:
open the form and click on the button, enter data and move from away from
the field, etc.) If the code's running, execution will break and you'll be
taken to that line of code just before it executes. When it breaks, hold
your mouse cursor over [txtAreaResp] to see what Access thinks the valueis
(or you can go to the Debug window, type ?txtAreaResp and hit Enter). Hit F8
to go to the next line of code: where do you end up?

news:871537f9-2745-4c98-8f60-c822bdaf6277@i76g2000hsf.googlegroups.com...

This is probably because I have trouble getting my head around
Nulls...
I have a text box and if there is a value (not null) I want to call a
module to email a report to a user. If there is no value (null) I
want it to do nothing.
I have tried:
If Len([txtAreaResp] & "") <> 0 Then
Assign
End If
Assign is the module I want to call to email the report to the user.
I know I have this wrong....Should I be using Nz?
Any help would be appreciated.

Hi Duane,

It skips over the "Assign" line and does not go to the module when
there is a value in the text box.

I even tried:  If Not IsNull(Me.txtAreaResp) Then

and I get the same results.- Hide quoted text -

- Show quoted text -

Thanks Duane....I am still new at debugging code..... I see what is
happening, but I am not
certain as to how to correct it.

Here is what I am trying to do.  The user is entering data from an
unbound form into a table.
Once the data is submitted to the table I would like the database to
email another user whose
area (as noted in the txtAreaResp) appears automatically.  I am using
a DEnd code in the
txtAreaResp textbox to identify the last updated area on the table so
as to call the email
module.

Now, I am wondering if I should work from a bound form to do
this......  The txtAreaResp
is coming up "Null" even after the re-query.- Hide quoted text -

- Show quoted text -

Nope, the bound form will not work with all the other controls I have
in
place on this form....any suggestions?

 

[Douglas J. Steele]

How is the data going into the table?

Not sure what you mean by "a DEnd code"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Are you sure that the If code is actually been run? Have you tried
single-stepping through the code?

Go into the VB Editor and click in the margin to the left of the If
statement. A large dot should appear in the margin, and the line of code
should be highlighted. Now, do whatever you do to make that code run
(i.e.:
open the form and click on the button, enter data and move from away from
the field, etc.) If the code's running, execution will break and you'll be
taken to that line of code just before it executes. When it breaks, hold
your mouse cursor over [txtAreaResp] to see what Access thinks the value
is
(or you can go to the Debug window, type ?txtAreaResp and hit Enter). Hit
F8
to go to the next line of code: where do you end up?

Thanks Duane....I am still new at debugging code..... I see what is
happening, but I am not
certain as to how to correct it.

Here is what I am trying to do. The user is entering data from an
unbound form into a table.
Once the data is submitted to the table I would like the database to
email another user whose
area (as noted in the txtAreaResp) appears automatically. I am using
a DEnd code in the
txtAreaResp textbox to identify the last updated area on the table so
as to call the email
module.

Now, I am wondering if I should work from a bound form to do
this...... The txtAreaResp
is coming up "Null" even after the re-query.

 

[Opal]

How is the data going into the table?

Not sure what you mean by "a DEnd code"

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Are you sure that the If code is actually been run? Have you tried
single-stepping through the code?

Go into the VB Editor and click in the margin to the left of the If
statement. A large dot should appear in the margin, and the line of code
should be highlighted. Now, do whatever you do to make that code run
(i.e.:
open the form and click on the button, enter data and move from away from
the field, etc.) If the code's running, execution will break and you'll be
taken to that line of code just before it executes. When it breaks, hold
your mouse cursor over [txtAreaResp] to see what Access thinks the value
is
(or you can go to the Debug window, type ?txtAreaResp and hit Enter). Hit
F8
to go to the next line of code: where do you end up?

Thanks Duane....I am still new at debugging code..... I see what is
happening, but I am not
certain as to how to correct it.

Here is what I am trying to do.  The user is entering data from an
unbound form into a table.
Once the data is submitted to the table I would like the database to
email another user whose
area (as noted in the txtAreaResp) appears automatically.  I am using
a DEnd code in the
txtAreaResp textbox to identify the last updated area on the table so
as to call the email
module.

Now, I am wondering if I should work from a bound form to do
this......  The txtAreaResp
is coming up "Null" even after the re-query.- Hide quoted text -

- Show quoted text -

I am using the following module to move the data from the form to the
table:

Sub Hourly()
On Error GoTo Err_Hourly_Click

Dim dbobject As DAO.Database
Dim HourlyRS As DAO.Recordset
Dim strquery As String

Set dbobject = CurrentDb
strquery = "SELECT * FROM HourlyStatus;"
Set HourlyRS = dbobject.OpenRecordset(strquery)

HourlyRS.AddNew
HourlyRS!LossDate = Forms![frmHourlyStatus]!txtDate.Value
HourlyRS!HourID = Forms![frmHourlyStatus]!HourID.Value
HourlyRS!BodyNo = Forms![frmHourlyStatus]!BodyNo.Value
HourlyRS!AreaID = Forms![frmHourlyStatus]!txtAreaResp.Value
HourlyRS!ShiftTimeID = Forms![frmHourlyStatus]!cboShiftTime.Value
HourlyRS!ShiftNameID = Forms![frmHourlyStatus]!cboShiftName.Value
HourlyRS!DefectDetail = Forms![frmHourlyStatus]!DefectDetail.Value
HourlyRS!ReasonLoss = Forms![frmHourlyStatus]!ReasonLoss.Value
HourlyRS!StatusID = Forms![frmHourlyStatus]!cboStatus.Value
HourlyRS!txtPictureFile = Forms![frmHourlyStatus]!txtPictureFile.Value
HourlyRS.Update

Dim ctlx As Controls
Dim ctl As Control
Set ctlx = Forms!frmHourlyStatus.Controls
For Each ctl In ctlx
If ctl.Tag = "Clear Me" Then
ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing

Exit_Hourly_Click:
Exit Sub

Err_Hourly_Click:
MsgBox Err.Description
Resume Exit_Hourly_Click

End Sub

as for the DEnd code, I found it at: http://support.microsoft.com/kb/210127

I want to ensure that the last record in the table is the one that is
emailed.

 

[Douglas J. Steele]

Your call to Assign should occur in that routine, since you know at that
point that you're creating a new entry.

Assuming that you've got an AutoNumber field in table HourlyStatus, you can
determine what value was assigned to the field by checking
HourlyRS!NameOfField before the HourlyRS.Update statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

How is the data going into the table?

Not sure what you mean by "a DEnd code"

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Are you sure that the If code is actually been run? Have you tried
single-stepping through the code?

Go into the VB Editor and click in the margin to the left of the If
statement. A large dot should appear in the margin, and the line of code
should be highlighted. Now, do whatever you do to make that code run
(i.e.:
open the form and click on the button, enter data and move from away
from
the field, etc.) If the code's running, execution will break and you'll
be
taken to that line of code just before it executes. When it breaks, hold
your mouse cursor over [txtAreaResp] to see what Access thinks the value
is
(or you can go to the Debug window, type ?txtAreaResp and hit Enter).
Hit
F8
to go to the next line of code: where do you end up?

Thanks Duane....I am still new at debugging code..... I see what is
happening, but I am not
certain as to how to correct it.

Here is what I am trying to do. The user is entering data from an
unbound form into a table.
Once the data is submitted to the table I would like the database to
email another user whose
area (as noted in the txtAreaResp) appears automatically. I am using
a DEnd code in the
txtAreaResp textbox to identify the last updated area on the table so
as to call the email
module.

Now, I am wondering if I should work from a bound form to do
this...... The txtAreaResp
is coming up "Null" even after the re-query.- Hide quoted text -

- Show quoted text -

I am using the following module to move the data from the form to the
table:

Sub Hourly()
On Error GoTo Err_Hourly_Click

Dim dbobject As DAO.Database
Dim HourlyRS As DAO.Recordset
Dim strquery As String

Set dbobject = CurrentDb
strquery = "SELECT * FROM HourlyStatus;"
Set HourlyRS = dbobject.OpenRecordset(strquery)

HourlyRS.AddNew
HourlyRS!LossDate = Forms![frmHourlyStatus]!txtDate.Value
HourlyRS!HourID = Forms![frmHourlyStatus]!HourID.Value
HourlyRS!BodyNo = Forms![frmHourlyStatus]!BodyNo.Value
HourlyRS!AreaID = Forms![frmHourlyStatus]!txtAreaResp.Value
HourlyRS!ShiftTimeID = Forms![frmHourlyStatus]!cboShiftTime.Value
HourlyRS!ShiftNameID = Forms![frmHourlyStatus]!cboShiftName.Value
HourlyRS!DefectDetail = Forms![frmHourlyStatus]!DefectDetail.Value
HourlyRS!ReasonLoss = Forms![frmHourlyStatus]!ReasonLoss.Value
HourlyRS!StatusID = Forms![frmHourlyStatus]!cboStatus.Value
HourlyRS!txtPictureFile = Forms![frmHourlyStatus]!txtPictureFile.Value
HourlyRS.Update

Dim ctlx As Controls
Dim ctl As Control
Set ctlx = Forms!frmHourlyStatus.Controls
For Each ctl In ctlx
If ctl.Tag = "Clear Me" Then
ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing

Exit_Hourly_Click:
Exit Sub

Err_Hourly_Click:
MsgBox Err.Description
Resume Exit_Hourly_Click

End Sub

as for the DEnd code, I found it at: http://support.microsoft.com/kb/210127

I want to ensure that the last record in the table is the one that is
emailed.

 

[Opal]

Your call to Assign should occur in that routine, since you know at that
point that you're creating a new entry.

Assuming that you've got an AutoNumber field in table HourlyStatus, you can
determine what value was assigned to the field by checking
HourlyRS!NameOfField before the HourlyRS.Update statement.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

How is the data going into the table?

Not sure what you mean by "a DEnd code"

Are you sure that the If code is actually been run? Have you tried
single-stepping through the code?
Go into the VB Editor and click in the margin to the left of the If
statement. A large dot should appear in the margin, and the line of code
should be highlighted. Now, do whatever you do to make that code run
(i.e.:
open the form and click on the button, enter data and move from away
from
the field, etc.) If the code's running, execution will break and you'll
be
taken to that line of code just before it executes. When it breaks, hold
your mouse cursor over [txtAreaResp] to see what Access thinks the value
is
(or you can go to the Debug window, type ?txtAreaResp and hit Enter).
Hit
F8
to go to the next line of code: where do you end up?

Thanks Duane....I am still new at debugging code..... I see what is
happening, but I am not
certain as to how to correct it.

Here is what I am trying to do. The user is entering data from an
unbound form into a table.
Once the data is submitted to the table I would like the database to
email another user whose
area (as noted in the txtAreaResp) appears automatically. I am using
a DEnd code in the
txtAreaResp textbox to identify the last updated area on the table so
as to call the email
module.

Now, I am wondering if I should work from a bound form to do
this...... The txtAreaResp
is coming up "Null" even after the re-query.- Hide quoted text -

- Show quoted text -

I am using the following module to move the data from the form to the
table:

Sub Hourly()
On Error GoTo Err_Hourly_Click

Dim dbobject As DAO.Database
Dim HourlyRS As DAO.Recordset
Dim strquery As String

Set dbobject = CurrentDb
strquery = "SELECT * FROM HourlyStatus;"
Set HourlyRS = dbobject.OpenRecordset(strquery)

HourlyRS.AddNew
HourlyRS!LossDate = Forms![frmHourlyStatus]!txtDate.Value
HourlyRS!HourID = Forms![frmHourlyStatus]!HourID.Value
HourlyRS!BodyNo = Forms![frmHourlyStatus]!BodyNo.Value
HourlyRS!AreaID = Forms![frmHourlyStatus]!txtAreaResp.Value
HourlyRS!ShiftTimeID = Forms![frmHourlyStatus]!cboShiftTime.Value
HourlyRS!ShiftNameID = Forms![frmHourlyStatus]!cboShiftName.Value
HourlyRS!DefectDetail = Forms![frmHourlyStatus]!DefectDetail.Value
HourlyRS!ReasonLoss = Forms![frmHourlyStatus]!ReasonLoss.Value
HourlyRS!StatusID = Forms![frmHourlyStatus]!cboStatus.Value
HourlyRS!txtPictureFile = Forms![frmHourlyStatus]!txtPictureFile.Value
HourlyRS.Update

Dim ctlx As Controls
Dim ctl As Control
Set ctlx = Forms!frmHourlyStatus.Controls
For Each ctl In ctlx
   If ctl.Tag = "Clear Me" Then
     ctl.Value = ""
   End If
Next ctl
Set ctlx = Nothing

Exit_Hourly_Click:
    Exit Sub

Err_Hourly_Click:
    MsgBox Err.Description
    Resume Exit_Hourly_Click

End Sub

as for the DEnd code, I found it at:  http://support.microsoft.com/kb/210127

I want to ensure that the last record in the table is the one that is
emailed.- Hide quoted text -

- Show quoted text -

Doug,

Are you saying that the Assign module should run before the
HourlyRS.Update? The report that is emailed to the Area users
is generated based on the data just updated to the table.

Yes, I do have an autonumber field in the HourlyStatus table.

I'm just a little confused.....

 

[Douglas J. Steele]

I'm saying that if you want an e-mail sent when a new entry is put into the
table, it only makes sense to call the code that sends the e-mail in the
same routine that inserts into the table.

You said "I want to ensure that the last record in the table is the one that
is emailed". I'm assuming what you really meant was "I want to email the
record that was just inserted.", in which case you can determine the ID of
the record that was just inserted while you're inserting it, and then use
that value to determine which record to email.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Your call to Assign should occur in that routine, since you know at that
point that you're creating a new entry.

Assuming that you've got an AutoNumber field in table HourlyStatus, you
can
determine what value was assigned to the field by checking
HourlyRS!NameOfField before the HourlyRS.Update statement.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

How is the data going into the table?

Not sure what you mean by "a DEnd code"

Are you sure that the If code is actually been run? Have you tried
single-stepping through the code?
Go into the VB Editor and click in the margin to the left of the If
statement. A large dot should appear in the margin, and the line of
code
should be highlighted. Now, do whatever you do to make that code run
(i.e.:
open the form and click on the button, enter data and move from away
from
the field, etc.) If the code's running, execution will break and
you'll
be
taken to that line of code just before it executes. When it breaks,
hold
your mouse cursor over [txtAreaResp] to see what Access thinks the
value
is
(or you can go to the Debug window, type ?txtAreaResp and hit Enter).
Hit
F8
to go to the next line of code: where do you end up?

Thanks Duane....I am still new at debugging code..... I see what is
happening, but I am not
certain as to how to correct it.

Here is what I am trying to do. The user is entering data from an
unbound form into a table.
Once the data is submitted to the table I would like the database to
email another user whose
area (as noted in the txtAreaResp) appears automatically. I am using
a DEnd code in the
txtAreaResp textbox to identify the last updated area on the table so
as to call the email
module.

Now, I am wondering if I should work from a bound form to do
this...... The txtAreaResp
is coming up "Null" even after the re-query.- Hide quoted text -

- Show quoted text -

I am using the following module to move the data from the form to the
table:

Sub Hourly()
On Error GoTo Err_Hourly_Click

Dim dbobject As DAO.Database
Dim HourlyRS As DAO.Recordset
Dim strquery As String

Set dbobject = CurrentDb
strquery = "SELECT * FROM HourlyStatus;"
Set HourlyRS = dbobject.OpenRecordset(strquery)

HourlyRS.AddNew
HourlyRS!LossDate = Forms![frmHourlyStatus]!txtDate.Value
HourlyRS!HourID = Forms![frmHourlyStatus]!HourID.Value
HourlyRS!BodyNo = Forms![frmHourlyStatus]!BodyNo.Value
HourlyRS!AreaID = Forms![frmHourlyStatus]!txtAreaResp.Value
HourlyRS!ShiftTimeID = Forms![frmHourlyStatus]!cboShiftTime.Value
HourlyRS!ShiftNameID = Forms![frmHourlyStatus]!cboShiftName.Value
HourlyRS!DefectDetail = Forms![frmHourlyStatus]!DefectDetail.Value
HourlyRS!ReasonLoss = Forms![frmHourlyStatus]!ReasonLoss.Value
HourlyRS!StatusID = Forms![frmHourlyStatus]!cboStatus.Value
HourlyRS!txtPictureFile = Forms![frmHourlyStatus]!txtPictureFile.Value
HourlyRS.Update

Dim ctlx As Controls
Dim ctl As Control
Set ctlx = Forms!frmHourlyStatus.Controls
For Each ctl In ctlx
If ctl.Tag = "Clear Me" Then
ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing

Exit_Hourly_Click:
Exit Sub

Err_Hourly_Click:
MsgBox Err.Description
Resume Exit_Hourly_Click

End Sub

as for the DEnd code, I found it at:
http://support.microsoft.com/kb/210127

I want to ensure that the last record in the table is the one that is
emailed.- Hide quoted text -

- Show quoted text -

Doug,

Are you saying that the Assign module should run before the
HourlyRS.Update? The report that is emailed to the Area users
is generated based on the data just updated to the table.

Yes, I do have an autonumber field in the HourlyStatus table.

I'm just a little confused.....

 

[Opal]

I'm saying that if you want an e-mail sent when a new entry is put into the
table, it only makes sense to call the code that sends the e-mail in the
same routine that inserts into the table.

You said "I want to ensure that the last record in the table is the one that
is emailed". I'm assuming what you really meant was "I want to email the
record that was just inserted.", in which case you can determine the ID of
the record that was just inserted while you're inserting it, and then use
that value to determine which record to email.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

Your call to Assign should occur in that routine, since you know at that
point that you're creating a new entry.

Assuming that you've got an AutoNumber field in table HourlyStatus, you
can
determine what value was assigned to the field by checking
HourlyRS!NameOfField before the HourlyRS.Update statement.

How is the data going into the table?
Not sure what you mean by "a DEnd code"
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)
On May 15, 7:06 am, "Douglas J. Steele"
Are you sure that the If code is actually been run? Have you tried
single-stepping through the code?
Go into the VB Editor and click in the margin to the left of the If
statement. A large dot should appear in the margin, and the line of
code
should be highlighted. Now, do whatever you do to make that code run
(i.e.:
open the form and click on the button, enter data and move from away
from
the field, etc.) If the code's running, execution will break and
you'll
be
taken to that line of code just before it executes. When it breaks,
hold
your mouse cursor over [txtAreaResp] to see what Access thinks the
value
is
(or you can go to the Debug window, type ?txtAreaResp and hit Enter)..
Hit
F8
to go to the next line of code: where do you end up?
Thanks Duane....I am still new at debugging code..... I see what is
happening, but I am not
certain as to how to correct it.
Here is what I am trying to do. The user is entering data from an
unbound form into a table.
Once the data is submitted to the table I would like the database to
email another user whose
area (as noted in the txtAreaResp) appears automatically. I am using
a DEnd code in the
txtAreaResp textbox to identify the last updated area on the table so
as to call the email
module.
Now, I am wondering if I should work from a bound form to do
this...... The txtAreaResp
is coming up "Null" even after the re-query.- Hide quoted text -
- Show quoted text -

I am using the following module to move the data from the form to the
table:

Sub Hourly()
On Error GoTo Err_Hourly_Click

Dim dbobject As DAO.Database
Dim HourlyRS As DAO.Recordset
Dim strquery As String

Set dbobject = CurrentDb
strquery = "SELECT * FROM HourlyStatus;"
Set HourlyRS = dbobject.OpenRecordset(strquery)

HourlyRS.AddNew
HourlyRS!LossDate = Forms![frmHourlyStatus]!txtDate.Value
HourlyRS!HourID = Forms![frmHourlyStatus]!HourID.Value
HourlyRS!BodyNo = Forms![frmHourlyStatus]!BodyNo.Value
HourlyRS!AreaID = Forms![frmHourlyStatus]!txtAreaResp.Value
HourlyRS!ShiftTimeID = Forms![frmHourlyStatus]!cboShiftTime.Value
HourlyRS!ShiftNameID = Forms![frmHourlyStatus]!cboShiftName.Value
HourlyRS!DefectDetail = Forms![frmHourlyStatus]!DefectDetail.Value
HourlyRS!ReasonLoss = Forms![frmHourlyStatus]!ReasonLoss.Value
HourlyRS!StatusID = Forms![frmHourlyStatus]!cboStatus.Value
HourlyRS!txtPictureFile = Forms![frmHourlyStatus]!txtPictureFile.Value
HourlyRS.Update

Dim ctlx As Controls
Dim ctl As Control
Set ctlx = Forms!frmHourlyStatus.Controls
For Each ctl In ctlx
If ctl.Tag = "Clear Me" Then
ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing

Exit_Hourly_Click:
Exit Sub

Err_Hourly_Click:
MsgBox Err.Description
Resume Exit_Hourly_Click

End Sub

as for the DEnd code, I found it at:
http://support.microsoft.com/kb/210127

I want to ensure that the last record in the table is the one that is
emailed.- Hide quoted text -

- Show quoted text -

Doug,

Are you saying that the Assign module should run before the
HourlyRS.Update?  The report that is emailed to the Area users
is generated based on the data just updated to the table.

Yes, I do have an autonumber field in the HourlyStatus table.

I'm just a little confused.....- Hide quoted text -

- Show quoted text -

Okay, that's correct, but how do I accomplish that?

 

[Opal]

I'm saying that if you want an e-mail sent when a new entry is put into the
table, it only makes sense to call the code that sends the e-mail in the
same routine that inserts into the table.

You said "I want to ensure that the last record in the table is the one that
is emailed". I'm assuming what you really meant was "I want to email the
record that was just inserted.", in which case you can determine the ID of
the record that was just inserted while you're inserting it, and then use
that value to determine which record to email.

Your call to Assign should occur in that routine, since you know at that
point that you're creating a new entry.
Assuming that you've got an AutoNumber field in table HourlyStatus, you
can
determine what value was assigned to the field by checking
HourlyRS!NameOfField before the HourlyRS.Update statement.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)
On May 15, 11:50 am, "Douglas J. Steele"
How is the data going into the table?
Not sure what you mean by "a DEnd code"
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)
On May 15, 7:06 am, "Douglas J. Steele"
Are you sure that the If code is actually been run? Have you tried
single-stepping through the code?
Go into the VB Editor and click in the margin to the left of the If
statement. A large dot should appear in the margin, and the line of
code
should be highlighted. Now, do whatever you do to make that code run
(i.e.:
open the form and click on the button, enter data and move from away
from
the field, etc.) If the code's running, execution will break and
you'll
be
taken to that line of code just before it executes. When it breaks,
hold
your mouse cursor over [txtAreaResp] to see what Access thinks the
value
is
(or you can go to the Debug window, type ?txtAreaResp and hit Enter).
Hit
F8
to go to the next line of code: where do you end up?
Thanks Duane....I am still new at debugging code..... I see what is
happening, but I am not
certain as to how to correct it.
Here is what I am trying to do. The user is entering data from an
unbound form into a table.
Once the data is submitted to the table I would like the database to
email another user whose
area (as noted in the txtAreaResp) appears automatically. I am using
a DEnd code in the
txtAreaResp textbox to identify the last updated area on the table so
as to call the email
module.
Now, I am wondering if I should work from a bound form to do
this...... The txtAreaResp
is coming up "Null" even after the re-query.- Hide quoted text -
- Show quoted text -
I am using the following module to move the data from the form to the
table:
Sub Hourly()
On Error GoTo Err_Hourly_Click
Dim dbobject As DAO.Database
Dim HourlyRS As DAO.Recordset
Dim strquery As String
Set dbobject = CurrentDb
strquery = "SELECT * FROM HourlyStatus;"
Set HourlyRS = dbobject.OpenRecordset(strquery)
HourlyRS.AddNew
HourlyRS!LossDate = Forms![frmHourlyStatus]!txtDate.Value
HourlyRS!HourID = Forms![frmHourlyStatus]!HourID.Value
HourlyRS!BodyNo = Forms![frmHourlyStatus]!BodyNo.Value
HourlyRS!AreaID = Forms![frmHourlyStatus]!txtAreaResp.Value
HourlyRS!ShiftTimeID = Forms![frmHourlyStatus]!cboShiftTime.Value
HourlyRS!ShiftNameID = Forms![frmHourlyStatus]!cboShiftName.Value
HourlyRS!DefectDetail = Forms![frmHourlyStatus]!DefectDetail.Value
HourlyRS!ReasonLoss = Forms![frmHourlyStatus]!ReasonLoss.Value
HourlyRS!StatusID = Forms![frmHourlyStatus]!cboStatus.Value
HourlyRS!txtPictureFile = Forms![frmHourlyStatus]!txtPictureFile.Value
HourlyRS.Update
Dim ctlx As Controls
Dim ctl As Control
Set ctlx = Forms!frmHourlyStatus.Controls
For Each ctl In ctlx
If ctl.Tag = "Clear Me" Then
ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing
Exit_Hourly_Click:
Exit Sub
Err_Hourly_Click:
MsgBox Err.Description
Resume Exit_Hourly_Click
End Sub
as for the DEnd code, I found it at:
http://support.microsoft.com/kb/210127
I want to ensure that the last record in the table is the one that is
emailed.- Hide quoted text -
- Show quoted text -

Doug,

Are you saying that the Assign module should run before the
HourlyRS.Update?  The report that is emailed to the Area users
is generated based on the data just updated to the table.

Yes, I do have an autonumber field in the HourlyStatus table.

I'm just a little confused.....- Hide quoted text -

- Show quoted text -

Okay, that's correct, but how do I accomplish that?- Hide quoted text -

- Show quoted text -

Doug, can you still assist?

 

[Douglas J. Steele]

Try putting your call to Assign inside the Hourly sub, right after the line

HourlyRS.Update


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm saying that if you want an e-mail sent when a new entry is put into
the
table, it only makes sense to call the code that sends the e-mail in the
same routine that inserts into the table.

You said "I want to ensure that the last record in the table is the one
that
is emailed". I'm assuming what you really meant was "I want to email the
record that was just inserted.", in which case you can determine the ID
of
the record that was just inserted while you're inserting it, and then
use
that value to determine which record to email.

Your call to Assign should occur in that routine, since you know at
that
point that you're creating a new entry.
Assuming that you've got an AutoNumber field in table HourlyStatus,
you
can
determine what value was assigned to the field by checking
HourlyRS!NameOfField before the HourlyRS.Update statement.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)
On May 15, 11:50 am, "Douglas J. Steele"
How is the data going into the table?
Not sure what you mean by "a DEnd code"
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)
On May 15, 7:06 am, "Douglas J. Steele"
Are you sure that the If code is actually been run? Have you tried
single-stepping through the code?
Go into the VB Editor and click in the margin to the left of the
If
statement. A large dot should appear in the margin, and the line
of
code
should be highlighted. Now, do whatever you do to make that code
run
(i.e.:
open the form and click on the button, enter data and move from
away
from
the field, etc.) If the code's running, execution will break and
you'll
be
taken to that line of code just before it executes. When it
breaks,
hold
your mouse cursor over [txtAreaResp] to see what Access thinks the
value
is
(or you can go to the Debug window, type ?txtAreaResp and hit
Enter).
Hit
F8
to go to the next line of code: where do you end up?
Thanks Duane....I am still new at debugging code..... I see what is
happening, but I am not
certain as to how to correct it.
Here is what I am trying to do. The user is entering data from an
unbound form into a table.
Once the data is submitted to the table I would like the database to
email another user whose
area (as noted in the txtAreaResp) appears automatically. I am using
a DEnd code in the
txtAreaResp textbox to identify the last updated area on the table
so
as to call the email
module.
Now, I am wondering if I should work from a bound form to do
this...... The txtAreaResp
is coming up "Null" even after the re-query.- Hide quoted text -
- Show quoted text -
I am using the following module to move the data from the form to the
table:
Sub Hourly()
On Error GoTo Err_Hourly_Click
Dim dbobject As DAO.Database
Dim HourlyRS As DAO.Recordset
Dim strquery As String
Set dbobject = CurrentDb
strquery = "SELECT * FROM HourlyStatus;"
Set HourlyRS = dbobject.OpenRecordset(strquery)
HourlyRS.AddNew
HourlyRS!LossDate = Forms![frmHourlyStatus]!txtDate.Value
HourlyRS!HourID = Forms![frmHourlyStatus]!HourID.Value
HourlyRS!BodyNo = Forms![frmHourlyStatus]!BodyNo.Value
HourlyRS!AreaID = Forms![frmHourlyStatus]!txtAreaResp.Value
HourlyRS!ShiftTimeID = Forms![frmHourlyStatus]!cboShiftTime.Value
HourlyRS!ShiftNameID = Forms![frmHourlyStatus]!cboShiftName.Value
HourlyRS!DefectDetail = Forms![frmHourlyStatus]!DefectDetail.Value
HourlyRS!ReasonLoss = Forms![frmHourlyStatus]!ReasonLoss.Value
HourlyRS!StatusID = Forms![frmHourlyStatus]!cboStatus.Value
HourlyRS!txtPictureFile = Forms![frmHourlyStatus]!txtPictureFile.Value
HourlyRS.Update
Dim ctlx As Controls
Dim ctl As Control
Set ctlx = Forms!frmHourlyStatus.Controls
For Each ctl In ctlx
If ctl.Tag = "Clear Me" Then
ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing
Exit_Hourly_Click:
Exit Sub
Err_Hourly_Click:
MsgBox Err.Description
Resume Exit_Hourly_Click
End Sub
as for the DEnd code, I found it at:
http://support.microsoft.com/kb/210127
I want to ensure that the last record in the table is the one that is
emailed.- Hide quoted text -

Doug,

Are you saying that the Assign module should run before the
HourlyRS.Update? The report that is emailed to the Area users
is generated based on the data just updated to the table.

Yes, I do have an autonumber field in the HourlyStatus table.

I'm just a little confused.....- Hide quoted text -

- Show quoted text -

Okay, that's correct, but how do I accomplish that?- Hide quoted text -

- Show quoted text -

Doug, can you still assist?

 

[Opal]

Try putting your call to Assign inside the Hourly sub, right after the line

HourlyRS.Update

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

I'm saying that if you want an e-mail sent when a new entry is put into
the
table, it only makes sense to call the code that sends the e-mail in the
same routine that inserts into the table.
You said "I want to ensure that the last record in the table is the one
that
is emailed". I'm assuming what you really meant was "I want to email the
record that was just inserted.", in which case you can determine the ID
of
the record that was just inserted while you're inserting it, and then
use
that value to determine which record to email.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)
On May 15, 3:16 pm, "Douglas J. Steele"
Your call to Assign should occur in that routine, since you know at
that
point that you're creating a new entry.
Assuming that you've got an AutoNumber field in table HourlyStatus,
you
can
determine what value was assigned to the field by checking
HourlyRS!NameOfField before the HourlyRS.Update statement.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)
On May 15, 11:50 am, "Douglas J. Steele"
How is the data going into the table?
Not sure what you mean by "a DEnd code"
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)
On May 15, 7:06 am, "Douglas J. Steele"
Are you sure that the If code is actually been run? Have you tried
single-stepping through the code?
Go into the VB Editor and click in the margin to the left of the
If
statement. A large dot should appear in the margin, and the line
of
code
should be highlighted. Now, do whatever you do to make that code
run
(i.e.:
open the form and click on the button, enter data and move from
away
from
the field, etc.) If the code's running, execution will break and
you'll
be
taken to that line of code just before it executes. When it
breaks,
hold
your mouse cursor over [txtAreaResp] to see what Access thinks the
value
is
(or you can go to the Debug window, type ?txtAreaResp and hit
Enter).
Hit
F8
to go to the next line of code: where do you end up?
Thanks Duane....I am still new at debugging code..... I see what is
happening, but I am not
certain as to how to correct it.
Here is what I am trying to do. The user is entering data from an
unbound form into a table.
Once the data is submitted to the table I would like the database to
email another user whose
area (as noted in the txtAreaResp) appears automatically. I am using
a DEnd code in the
txtAreaResp textbox to identify the last updated area on the table
so
as to call the email
module.
Now, I am wondering if I should work from a bound form to do
this...... The txtAreaResp
is coming up "Null" even after the re-query.- Hide quoted text -
- Show quoted text -
I am using the following module to move the data from the form to the
table:
Sub Hourly()
On Error GoTo Err_Hourly_Click
Dim dbobject As DAO.Database
Dim HourlyRS As DAO.Recordset
Dim strquery As String
Set dbobject = CurrentDb
strquery = "SELECT * FROM HourlyStatus;"
Set HourlyRS = dbobject.OpenRecordset(strquery)
HourlyRS.AddNew
HourlyRS!LossDate = Forms![frmHourlyStatus]!txtDate.Value
HourlyRS!HourID = Forms![frmHourlyStatus]!HourID.Value
HourlyRS!BodyNo = Forms![frmHourlyStatus]!BodyNo.Value
HourlyRS!AreaID = Forms![frmHourlyStatus]!txtAreaResp.Value
HourlyRS!ShiftTimeID = Forms![frmHourlyStatus]!cboShiftTime.Value
HourlyRS!ShiftNameID = Forms![frmHourlyStatus]!cboShiftName.Value
HourlyRS!DefectDetail = Forms![frmHourlyStatus]!DefectDetail.Value
HourlyRS!ReasonLoss = Forms![frmHourlyStatus]!ReasonLoss.Value
HourlyRS!StatusID = Forms![frmHourlyStatus]!cboStatus.Value
HourlyRS!txtPictureFile = Forms![frmHourlyStatus]!txtPictureFile.Value
HourlyRS.Update
Dim ctlx As Controls
Dim ctl As Control
Set ctlx = Forms!frmHourlyStatus.Controls
For Each ctl In ctlx
If ctl.Tag = "Clear Me" Then
ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing
Exit_Hourly_Click:
Exit Sub
Err_Hourly_Click:
MsgBox Err.Description
Resume Exit_Hourly_Click
End Sub
as for the DEnd code, I found it at:
http://support.microsoft.com/kb/210127
I want to ensure that the last record in the table is the one that is
emailed.- Hide quoted text -
Doug,
Are you saying that the Assign module should run before the
HourlyRS.Update? The report that is emailed to the Area users
is generated based on the data just updated to the table.
Yes, I do have an autonumber field in the HourlyStatus table.
I'm just a little confused.....- Hide quoted text -
- Show quoted text -

Okay, that's correct, but how do I accomplish that?- Hide quoted text -

- Show quoted text -

Doug, can you still assist?- Hide quoted text -

- Show quoted text -

Hi Doug,

I will try on Tuesday (long weekend! Yeah!) I hope you will still be
around
to hold my hand with this next week....?

 

[Opal]

Try putting your call to Assign inside the Hourly sub, right after the line

"Opal" <[email protected]> wrote in message

On May 15, 5:25 pm, "Douglas J. Steele"
I'm saying that if you want an e-mail sent when a new entry is put into
the
table, it only makes sense to call the code that sends the e-mail inthe
same routine that inserts into the table.
You said "I want to ensure that the last record in the table is the one
that
is emailed". I'm assuming what you really meant was "I want to emailthe
record that was just inserted.", in which case you can determine theID
of
the record that was just inserted while you're inserting it, and then
use
that value to determine which record to email.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)
On May 15, 3:16 pm, "Douglas J. Steele"
Your call to Assign should occur in that routine, since you know at
that
point that you're creating a new entry.
Assuming that you've got an AutoNumber field in table HourlyStatus,
you
can
determine what value was assigned to the field by checking
HourlyRS!NameOfField before the HourlyRS.Update statement.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)
On May 15, 11:50 am, "Douglas J. Steele"
How is the data going into the table?
Not sure what you mean by "a DEnd code"
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)
On May 15, 7:06 am, "Douglas J. Steele"
Are you sure that the If code is actually been run? Have you tried
single-stepping through the code?
Go into the VB Editor and click in the margin to the left of the
If
statement. A large dot should appear in the margin, and the line
of
code
should be highlighted. Now, do whatever you do to make that code
run
(i.e.:
open the form and click on the button, enter data and move from
away
from
the field, etc.) If the code's running, execution will break and
you'll
be
taken to that line of code just before it executes. When it
breaks,
hold
your mouse cursor over [txtAreaResp] to see what Access thinksthe
value
is
(or you can go to the Debug window, type ?txtAreaResp and hit
Enter).
Hit
F8
to go to the next line of code: where do you end up?
Thanks Duane....I am still new at debugging code..... I see whatis
happening, but I am not
certain as to how to correct it.
Here is what I am trying to do. The user is entering data from an
unbound form into a table.
Once the data is submitted to the table I would like the database to
email another user whose
area (as noted in the txtAreaResp) appears automatically. I am using
a DEnd code in the
txtAreaResp textbox to identify the last updated area on the table
so
as to call the email
module.
Now, I am wondering if I should work from a bound form to do
this...... The txtAreaResp
is coming up "Null" even after the re-query.- Hide quoted text -
- Show quoted text -
I am using the following module to move the data from the form to the
table:
Sub Hourly()
On Error GoTo Err_Hourly_Click
Dim dbobject As DAO.Database
Dim HourlyRS As DAO.Recordset
Dim strquery As String
Set dbobject = CurrentDb
strquery = "SELECT * FROM HourlyStatus;"
Set HourlyRS = dbobject.OpenRecordset(strquery)
HourlyRS.AddNew
HourlyRS!LossDate = Forms![frmHourlyStatus]!txtDate.Value
HourlyRS!HourID = Forms![frmHourlyStatus]!HourID.Value
HourlyRS!BodyNo = Forms![frmHourlyStatus]!BodyNo.Value
HourlyRS!AreaID = Forms![frmHourlyStatus]!txtAreaResp.Value
HourlyRS!ShiftTimeID = Forms![frmHourlyStatus]!cboShiftTime.Value
HourlyRS!ShiftNameID = Forms![frmHourlyStatus]!cboShiftName.Value
HourlyRS!DefectDetail = Forms![frmHourlyStatus]!DefectDetail.Value
HourlyRS!ReasonLoss = Forms![frmHourlyStatus]!ReasonLoss.Value
HourlyRS!StatusID = Forms![frmHourlyStatus]!cboStatus.Value
HourlyRS!txtPictureFile = Forms![frmHourlyStatus]!txtPictureFile..Value
HourlyRS.Update
Dim ctlx As Controls
Dim ctl As Control
Set ctlx = Forms!frmHourlyStatus.Controls
For Each ctl In ctlx
If ctl.Tag = "Clear Me" Then
ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing
Exit_Hourly_Click:
Exit Sub
Err_Hourly_Click:
MsgBox Err.Description
Resume Exit_Hourly_Click
End Sub
as for the DEnd code, I found it at:
http://support.microsoft.com/kb/210127
I want to ensure that the last record in the table is the one thatis
emailed.- Hide quoted text -
Doug,
Are you saying that the Assign module should run before the
HourlyRS.Update? The report that is emailed to the Area users
is generated based on the data just updated to the table.
Yes, I do have an autonumber field in the HourlyStatus table.
I'm just a little confused.....- Hide quoted text -
- Show quoted text -
Okay, that's correct, but how do I accomplish that?- Hide quoted text -
- Show quoted text -

Doug, can you still assist?- Hide quoted text -

- Show quoted text -

Hi Doug,

I will try on Tuesday (long weekend!  Yeah!)  I hope you will still be
around
to hold my hand with this next week....?- Hide quoted text -

- Show quoted text -

Works great, Doug! Thank you so much!