Data mismatch............can find form??

[JNariss]

Hello,

I am trying to include some fields into my emails using the SendObject
and creating a recordset to find the values to include into the
SendObject. However I keep getting the error:

Can't find the form 'Move Request' referred to in a macro expression or
visual basic code.

So here's the code I am trying to use:

Private Sub Submit_Click()
On Error GoTo Err_Submit_Click

Dim strRequest_ID As String
Dim strDate As String
Dim strAuthorized_By As String
Dim strMessage As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * From [Move Request] WHERE [Move
#]" & " = " & Forms![Move Request]![Request ID])

strMove_# = rst![Move #]
strRequest_ID = Me.Request_ID
strDate = Me.Date
strAuthorized_By = Me.Authorized_By
strMessage = "The following Request has been submitted for production.
For more information regarding this request please go to the database
at \\egsrosintra1\d$\Database\SystemChangeRequest.mdb " & Chr$(13) &
Chr$(13) & _
"Request ID: " & strRequest_ID & Chr$(13) & Chr$(13) & _
"Move #: " & strMove_# & Chr$(13) & Chr$(13) & _
"Date: " & strDate & Chr(13) & Chr(13) & _
"Authorized By: " & strAuthorized_By & Chr(13) & Chr(13) & Chr(13) &
Chr(13) & _
"Please do not reply to this automated email."

DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)", , ,
"Submit To Production", strMessage, No, False

DoCmd.GoToRecord , , acNewRec
DoCmd.Close acForm, "Submit To Production", acSaveYes

MsgBox "You have successfully submitted your request for production",
vbOKOnly
Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click

End Sub


NOW.........here is what I want to do:

The form this code is coming out of is called Submit To Production.
This form only has 3 fields:

1. Request ID
2. Date
3. Authorized By

However...When the form gets submitted I would like it to find the
matching Move # which is in my other table called "Move Request".
However this Move # has to match up with the Request ID number entered
in the Submit To Production form. I thought by somehow using the
recordset code I could make that happen. But I'm obviously doing
something incorrect.

 

[Rob Oldfield]

If your form is called Submit To Production then why are you trying to
retrieve a value of a control from a form called Move Request?

Try:

Set rst = db.OpenRecordset("SELECT * From [Move Request] WHERE [Move#]" & "
= " & Forms![Submit To Production]![Request ID])

 

[JNariss]

Hi Rob,

Thanks for the reply. I tried the code you provided me with and I
received the error: Too few parameters expected 1. The full code I am
using is:

Private Sub Submit_Click()
On Error GoTo Err_Submit_Click

Dim strRequest_ID As String
Dim strDate As String
Dim strAuthorized_By As String
Dim strMessage As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * From [Move Request] WHERE [Move
#]" & " = " & Forms![Submit To Production]![Request ID])

strMove_# = rst![Move #]
strRequest_ID = Me.Request_ID
strDate = Me.Date
strAuthorized_By = Me.Authorized_By
strMessage = "The following Request has been submitted for production.
For more information regarding this request please go to the database
at \\egsrosintra1\d$\Database\SystemChangeRequest.mdb " & Chr$(13) &
Chr$(13) & _
"Request ID: " & strRequest_ID & Chr$(13) & Chr$(13) & _
"Move #: " & strMove_# & Chr$(13) & Chr$(13) & _
"Date: " & strDate & Chr(13) & Chr(13) & _
"Authorized By: " & strAuthorized_By & Chr(13) & Chr(13) & Chr(13) &
Chr(13) & _
"Please do not reply to this automated email."

DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)", , ,
"Submit To Production", strMessage, No, False
'DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)", ,
, "Submit To Production", "A Request has been submitted to production,
please see the database for more information.", No, False
DoCmd.GoToRecord , , acNewRec
DoCmd.Close acForm, "Submit To Production", acSaveYes
MsgBox "You have successfully submitted your request for
production", vbOKOnly
Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click

End Sub


I know that specific error is usually b/c of a mis-spelling but
everything is spelt correclty. Do you see anything incorrect?

 

[Rob Oldfield]

Move Request is a query that includes a reference to a control on a form (or
is based on another query which has one)?

 

[JNariss]

Move Request is a table. It includes the fields Request ID, Move #,
Date, Analyst, and Analyst Comments. The user goes into the Move
Request form I created and enters the Request ID number from the
original Request they filled out and then enters a Move #, Date, their
Name (Analyst), and any comments they may have. The Move # they enter
is given to them elsewhere (by their manager) so they simply enter it
manually.

Every user starts this database by filling out a Request and then this
gives them their Request ID # (an auto number field). The Request ID is
what ties all my tables together. However when the user fills out the
Submit To Production form they want to be able to see the corresponding
Move # to their Request ID number. But the problem I am having is
trying to pull the Move # correctly from the Move Request table into
the Submit To Production email. I did not think this would be such a
problem to do considering each table is tied with the Request ID.

 

[Rob Oldfield]

I don't get what is going wrong, but before I come back to that, I don't
understand what it is that you're trying to do. The user fills out a
request and gets an ID from that - OK, I've got that - that comes from the
Move Request table, yes?

What happens next? What does the Move # relate to? And how does it get
generated? It comes from a different table? In this code that we're
talking about, what is the user trying to retrieve?

 

[JNariss]

Ok here goes:

1. User opens database and clicks on "Add a New Request" on my
switchboard
- This opens the "Request" form
- The Request ID is an auto-number on the form
2. Request is sent to manager
3. Manager goes into database and clicks "Assign Analyst"
- Manager enters the Request ID they want to assign the Analyst
to and then chooses an Analyst from my listbox
- Manager clicks send and an email is sent to the Analyst
notifying them they have been assigned a Request.
- The email to the Analyst contains the Request ID number so
they know the Request they have been assigned to
4. The Analyst then goes to another system we have called "Silvon"
(this is where they receive the Move #)
5. Analyst goes into my database and clicks on "Move Request"
- Analyst enters the Request ID number, Move # (a text data
field), Date, Analyst Name (their name) and Comments
- Analyst clicks submit. (information from the Move Request is
stored in the Move Request table and sent to my group of managers
notifying them the Move Request has been submitted)
- The email sent to the group of managers includes all the fields
in the Move Request form that have been filled out
6. Manager goes into my database and clicks "Submit to Production"
- Manager types in the Request ID number they want to submit to
production, chooses a date, and types their name
- Manager clicks submit (information is sent in an email to a group
of managers. This information includes all the fields from the Submit
to Production form)

Here is where the problems generates..........The managers receiving
the email from the Submit to Production form would like the email to
include the Move # that was entered from the "Move Request" form in
step 5.

I hope this explains what I am trying to do. I really appreciate you
trying to help me figure this out.

Thanks

 

[Rob Oldfield]

At step 4...

4. The Analyst then goes to another system we have called "Silvon"
(this is where they receive the Move #)

.... does that get written to your database? If so, into which table? If it
is into a different table, what's the relationship to the request table (one
to one or one to many)? Or is it a totally independent system?

 

[JNariss]

The "Silvon" system is totally independent from my database. The user
just retrieves that # and then manually types it into the Move Request
form (into a text field data type). So I just want to connect the
Request ID # that is entered into the Submit To Production form and
match it with the Move # that was entered in the Move Request form and
have both numbers appear on the email that is sent.

 

[Rob Oldfield]

So the user could uniquely identify a record by using either of Request ID
or Move #?

(Sorry if it seems that I'm asking questions that don't appear to
specifically address your issue, I'm just figuring out the best way of doing
it.)

 

[JNariss]

Technically yes, I could run a query to view by the move # through the
Move Request Table. However my Request Table is the main table and it
does not contain the Move #. Move # is only in one table and that is
Move Request. Also, the only way someone could identify a record by
Move # is if the user completed steps 1 - 4 above because the Move #
does not get entered until step 5.
It's like a big work flow process - one step leads to the next and
people are too lazy to view the reports I have made for the database,
instead they want to see all the info in their email.

 

[Rob Oldfield]

OK. Apologies for getting sidetracked but it seemed a bit of a strange way
to approach it. Now that I see that it's not....

What happens if you try hardcoding a specific value into your openrecordset
statement? e.g.

Set rst = db.OpenRecordset("SELECT * From [Move Request] WHERE [Move #] = 3)

...where 3 is a valid value.

A couple of other points:
The DoCmd.GoToRecord , , acNewRec and the acSaveYes in DoCmd.Close acForm,
"Submit To Production", acSaveYes is unnecessary.

The record is automatically saved when you close the form and you'd only
need the acSaveYes if you are saving design changes to the form.

Instead of the chr(13)s take a look at the vbCrLf constant:

"a"+vbcrlf+"b" comes out as:
a
b

....and please tell me that the d drive on egsrosintra1 is purely data. Even
so, I hate the idea of giving users access to hidden shares, and particular
root ones.