Force a Selection (via Click) From a Drop Down Box

[ElizaD]

I am hoping someone can help me with a problem that has been plaguing me for
months.

I have a database where a form has a command button the user clicks to
update a record with the current date in a table. This date represents the
day they completed work on a request send by another group. When they click
on this button, I have a macro that runs in the background that pulls all
open request numbers into a temporary table. A form then opens for the user
to select the request they want to close from this temp table. After they
select the request from a drop down list, they close the form via a command
button which starts another macro that populates the date for the request
number selected in the master table and generates an email.

I have one user who occasionally gets blank emails and the dates do not
populate. The only way I can reproduce this is if I scroll through the
requests but never actually click on one (so basically nothing is selected).
No one else has this problem, but she insists she clicks on her selection
every time. My only other thought is her Access is flawed and needs to be
reloaded on her PC. She took over this job not too long ago and I found out
today that she is using the same PC and the person she replaced who
incidentally also had the same issue.

What I would like to do is either make it so that they have to click on a
record to generate the rest of the process (eliminating the close button on
the form so they have no choice) or be able to generate an error message if
nothing is selected when they hit the close button. I have no clue how to go
about either one of these solutions. Any suggestions?

 

[Steve Sanford]

Your description is a little hard to follow, but i think I understand.......
it would help to have object names.

I wouldn't close the form based on a selection. If someone twitched and
selected the wrong request, how would they be able to select the correct
request? I is easier to check if a value is selected before continuing the
process.

If you would, please post the code for the buttons.

Thanks

 

[ElizaD]

Here you go. I apologize for not being clear. Hopefully this helps.

Step 1: Press Update Completion Date-BAR button
Underlying code:

Private Sub UpdateBARCompDt_Click()
On Error GoTo Err_UpdateBARCompDt_Click

Dim stDocName As String

stDocName = "8200 Update BAR Completion Date"
DoCmd.RunMacro stDocName

stDocName = "1000b Clear Email Information"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_UpdateBARCompDt_Click:
Exit Sub

Err_UpdateBARCompDt_Click:
MsgBox Err.Description
Resume Exit_UpdateBARCompDt_Click

End Sub

Macro 8200 Update BAR Completion Date consists of 2 queries:
1) Makes a table of all open request numbers and
2) Opens the form for the user to select a request from that table
Query 1000b Clear Email Information clears the email information from the
table I use to hold it.

Step 2: Form is open (from Step 1), Select desired request number, press
close button
Underlying code:
Private Sub CloseReqNo_Click()
On Error GoTo Err_CloseReqNo_Click

DoCmd.Close

Dim stDocName As String

stDocName = "8200a Update BAR Completion Date"
DoCmd.RunMacro stDocName

Call EmailPFStoFinance_Click

stDocName = "8203 Delete Gather Email Data Table"
DoCmd.RunMacro stDocName

Exit_CloseReqNo_Click:
Exit Sub

Err_CloseReqNo_Click:
MsgBox Err.Description
Resume Exit_CloseReqNo_Click

End Sub


Private Sub EmailPFStoFinance_Click()
On Error GoTo Err_EmailPFStoFinance_Click

Dim varTo As Variant '-- To Address for SendObject
Dim varFrom As Variant '-- From Address for SendObject
Dim strText As Variant '-- E-mail text
Dim CompDate As Variant '-- Completion date requested for e-mail text
Dim ReqDate As Variant '--Original requested date
Dim strSubject As Variant '-- Subject line of e-mail
Dim strReqNo As Variant '-- The request number from form
Dim strPriority As Variant '-- Priority status for email text
Dim strRequestor As Variant '-- Person making request for email text
Dim strDict As Variant '-- Dictionary updated
Dim strReqDesc As Variant 'Request description

strPriority = DLookup("[Priority Level]", "TEMP_PFS to Finance Email
Information2")
strReqNo = DLookup("[Request Number]", "TEMP_PFS to Finance Email
Information2")
ReqDate = DLookup("[Date Requested]", "TEMP_PFS to Finance Email
Information2")
CompDate = DLookup("[Completion Date Requested]", "TEMP_PFS to Finance
Email Information2")
strRequestor = DLookup("[Requestor]", "TEMP_PFS to Finance Email
Information2")
strDict = DLookup("[Dictionary]", "TEMP_PFS to Finance Email
Information2")
strReqDesc = DLookup("[Request Description]", "TEMP_PFS to Finance Email
Information2")

varTo = DLookup("[Email Address]", "TEMP_PFS to Finance Email
Information2")
varFrom = "(e-mail address removed)"
strSubject = "GE/IDX Request Completed: " & strReqDesc

strText = "The requested chargemaster update for BAR (D1) has been
completed." & Chr$(13) & _
Chr$(10) & "Priority Level: " & strPriority & Chr$(13) & _
Chr$(10) & "Dictionary: " & strDict & Chr$(13) & _
Chr$(10) & "Request Number: " & strReqNo & Chr$(13) & _
Chr$(10) & "Request Description: " & strReqDesc & Chr$(13) & _
Chr$(10) & "Requested By: " & strRequestor & Chr$(13) & _
Chr$(10) & "Date Requested: " & ReqDate & Chr$(13) & _
Chr$(10) & "Completion Date Requested: " & CompDate

'Write the e-mail content for sending to PFS Maintenance
DoCmd.SendObject , , acFormatTXT, varTo, , , strSubject, strText, -1


Exit_EmailPFStoFinance_Click:
Exit Sub

Err_EmailPFStoFinance_Click:
MsgBox Err.Description
Resume Exit_EmailPFStoFinance_Click

End Sub

Macro 8200a Update BAR Completion Date consists of 2 queries
1) Update the date completed in master table based on the request number
selected
2) Gather the email information needed based on the request number selected

Macro 8203 Delete Gather Email Data Table consists of 3 DeleteObject
actions to delete the temp tables.

 

[Steve Sanford]

OK, I think I understand.

Form "Update Completion Date-BAR" has a button "UpdateBARCompDt_Click" which
opens another form (I'll call it "FORM2") with a button "CloseReqNo_Click"
and a combo box (I'll call "COMBO1"

After a request number is selected from "COMBO1", the button
"CloseReqNo_Click" is clicked. Looking at the code, the first statement
executed is "Docmd.Close" which closes the form ("FORM2"). But the request
number is not save/stored (unless there is code behind the combo box.) How
does the macro "8200a Update BAR Completion Date" know which request number
was selected???


I would move the line "Docmd.Close" to just above the Exit Sub. Also, I
would check if a selection was made in COMBO1.

Note: change COMBO1 to the name of your combo box!

Try this:

'-----------------------------------------------------
'Step 2: Form is open (from Step 1), Select desired request number, press
close button
'Underlying code:

Private Sub CloseReqNo_Click()
On Error GoTo Err_CloseReqNo_Click

Dim stDocName As String

'request selected?? ********
If Len(Nz(Me.COMBO1, "")) = 0 Then
MsgBox "Please select a request number", vbExclamation + vbOKOnly, "No
Selection"
End If ' ********

'Macro 8200a Update BAR Completion Date consists of 2 queries
'1) Update the date completed in master table based on the request
number selected
'2) Gather the email information needed based on the request number
selected

stDocName = "8200a Update BAR Completion Date"
DoCmd.RunMacro stDocName

'create & send email
Call EmailPFStoFinance_Click

'Macro 8203 Delete Gather Email Data Table consists of
' 3 DeleteObject actions to delete the temp tables.
stDocName = "8203 Delete Gather Email Data Table"
DoCmd.RunMacro stDocName

'close FORM2 ********
DoCmd.Close

Exit_CloseReqNo_Click:
Exit Sub

Err_CloseReqNo_Click:
MsgBox Err.Description
Resume Exit_CloseReqNo_Click

End Sub
'-----------------------------------------------------


If the above changes doesn't solve the problem, it would be nice to see the
Macros and the SQL of the queries.

'=================

Other things to consider:

1) Do not use spaces in object names. See
http://mvps.org/access/tencommandments.htm

2) I never use macros - no error handling. Access has a wizard to convert
macros to VBA.

3) Get in the habit of commenting your code - even simple a simple
Sub/Function

4) I do not like using make table queries in my production MDB. If you use
the table more than once, create the table and delete the data before each
use. Less bloat and less chance of corruption (IMO). You can delete the data
in a table with one line:

CurrentDb.Execute "DELETE * FROM YourTableName", dbFailOnError




HTH

 

[ElizaD]

Thanks, this is what I was looking for. It may be a few weeks before I know
if it fixes the problem.

There is no SQL behind the button other than what I sent you. I have a query
that makes a table with all open request numbers. The form opens a completely
different table that is linked to the open request number table, using a drop
down box forcing them to select from the list. I didn't know any other way to
do this. Probably not the most elegant solution, but this particular database
stretched my limited knowledge quite a bit. I will also read up on the other
considerations you mentioned. It may be time for a version 2 of this
particular database )

 

[Steve Sanford]

I would be willing to take a look at the MDB....I won't make any promises,
but maybe I could make a couple of suggestions. If you decide to send it to
me, please remove/change any sensitive info, do a Complie & Repair, then Zip
it. (BTW, I have Access 2K.)