If Then? Select Case?

  • Thread starter Thread starter Rosemary
  • Start date Start date
R

Rosemary

Hi - I have two quesstions please. How would I code the following --

We have WindowsXP, and MS Office 2003 in a network environment. I want to
add code to my MS Access database application as follows:

Question 1:

Two combo boxes (both drop-down list boxes) involved are:
cboRequestorName
cboLibraryLocation

LibraryLocation refers to the area on the server designated for each of 7
branch offices, and each branch office has its files stored in its own
Library. Each branch office is identified by its city name (e.g., Albany,
Buffalo, etc.). Each of the 7 Libraries has its own list of RequestorNames.

So essentially, what I want to code is: when a RequestorName is selected by
the user, I want the appropriate Library to autopopulate in the
LibraryLocation combo box.

PseudoCode:

If cboRequestorName is Johnson, Michael
or If cboRequestorName is Price, Cynthia
or If cboRequestorName is Jones, Phillip
or If cboRequestorName is Smith, Jane
Then
cboLibraryLocation = Albany

If cboRequestorName is Peters, Mark
or If cboRequestorName is Baldwin, Anna
or If cboRequestorName is Devon, Eric
or If cboRequestorName is Bryce, Stanley
Then
cboLibraryLocation = Buffalo

.... etc. for all 7 branch office Libraries

Question 2:

There is a command button control named cmdProcesses, which is set in the
header section of the Main form, named frmMain. Clicking cmdProcesses opens
a subform named frmProcesses. In this subform there are two text boxes,
named txtStartDateTime and txtEndDateTime.

If txtStartDateTime is filled in, but txtEndDateTime is empty, it means a
job is in progress. I want the command button to reflect whether a job is in
progress or not by its color.

So, if txtStartDateTime is filled in (e.g., 3/2/2008, 5:55:00PM) but
txtEndDateTime is empty, I want either the forecolor or the backcolor of
cmdProcessses to change to red (or some other property that makes it stand
out).

Many thanks!!
Rosemary
 
Rosemary

Question 1

Select Case cboRequestorName

Case "Johnson, Michael","Price, Cynthia", etc
cboLibraryLocation = "Albany"

Case "Peters, Mark","Baldwin, Anna",etc
cboLibraryLocation = "Buffalo"
etc for the remaining branches

End select

Allan
 
Rosemary said:
Hi - I have two quesstions please. How would I code the following --

We have WindowsXP, and MS Office 2003 in a network environment. I want to
add code to my MS Access database application as follows:

Question 1:

Two combo boxes (both drop-down list boxes) involved are:
cboRequestorName
cboLibraryLocation

LibraryLocation refers to the area on the server designated for each of 7
branch offices, and each branch office has its files stored in its own
Library. Each branch office is identified by its city name (e.g., Albany,
Buffalo, etc.). Each of the 7 Libraries has its own list of RequestorNames.

So essentially, what I want to code is: when a RequestorName is selected by
the user, I want the appropriate Library to autopopulate in the
LibraryLocation combo box.

PseudoCode:

If cboRequestorName is Johnson, Michael
or If cboRequestorName is Price, Cynthia
or If cboRequestorName is Jones, Phillip
or If cboRequestorName is Smith, Jane
Then
cboLibraryLocation = Albany

If cboRequestorName is Peters, Mark
or If cboRequestorName is Baldwin, Anna
or If cboRequestorName is Devon, Eric
or If cboRequestorName is Bryce, Stanley
Then
cboLibraryLocation = Buffalo

... etc. for all 7 branch office Libraries
Click to expand...

That is a really poor way to display the location because
the code includes data values. What happens when you add a
new requestor? It would be a maintenace nightmare to have
to modify your code with every personel change.

I don't know what your tables look like, but the requestors
table should have a foreign key to the locations table. In
that case, each requestor is assigned to a location and the
combo box's row source query can include the location. The
combo box's row source query might then be something like:

SELECT R.RequestorID, R.RequestorName, L.City
FROM RequestorNames As R INNER JOIN Locations As L
ON R.RequestorLocation = L.LocationID

With that query, the combo box's properties would be:
BoundColumn 1
ColumnCount 3
ColumnWidths 0;;0

Given that all that is sorted out properly, a text box can
display the library location by using an expression like:
=cboRequestorName.Column(2)
withour using any code.
--
Marsh
MVP [MS Access]

Question 2:

There is a command button control named cmdProcesses, which is set in the
header section of the Main form, named frmMain. Clicking cmdProcesses opens
a subform named frmProcesses. In this subform there are two text boxes,
named txtStartDateTime and txtEndDateTime.

If txtStartDateTime is filled in, but txtEndDateTime is empty, it means a
job is in progress. I want the command button to reflect whether a job is in
progress or not by its color.

So, if txtStartDateTime is filled in (e.g., 3/2/2008, 5:55:00PM) but
txtEndDateTime is empty, I want either the forecolor or the backcolor of
cmdProcessses to change to red (or some other property that makes it stand
out).
Click to expand...


This needs to be clarified (a subform is not "opened"). If
it truely is a subform, then I do not understand what
purpose the button serves.

If it is a separate, albeit related form, then the button's
color depends on the data in the table that you want to
display in the processes form. This can be done with a
DLookup function, but it depends on how the two tables are
related.
 
Hi - Thanks for your reply.

The error was probably that I identified it as a subform. It is actually
just a separate form.

To explain further - my application is a workflow tracking system.

The Main form is used by word processors to track the jobs they work on.
Each job is given a job number (that record's autonumber), and pertinent
information is entered in the Main form about the job (Requestor, branch
office, document name, network number, client code, etc.).

In order to sign on and off of a job, the word processor clicks the
cmdProcesses button (caption is "Operator"). When the word processor clicks
the button, the Processes form opens, and it has two text boxes - one to
enter the date/time they started the job, and one to enter the date/time they
finished the job.

In the OnClick event of cmdProcesses is the following code:

-------------------------------------------------------------------
Private Sub cmdOperator_Click()
On Error GoTo Err_cmdOperator_Click

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "JobId = " & Me.JobId
stDocName = "frmProcesses"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

Exit_cmdOperator_Click:
Exit Sub

Err_cmdOperator_Click:
MsgBox Err.Description
Resume Exit_cmdOperator_Click

End Sub
-------------------------------------------------------------------

The Record Source for frmProcesses is the Processes table, which is linked
to the Employees table in a one (Employees) to many (Processes) relationship
via the EmpId field.

Eesh. Sorry for the loooong explanation. I hope it makes sense.

Does this clarify my question?

Regards,
Rosemary



This needs to be clarified (a subform is not "opened"). If
 
Thanks, Allan!


Allan Murphy said:
Rosemary

Question 1

Select Case cboRequestorName

Case "Johnson, Michael","Price, Cynthia", etc
cboLibraryLocation = "Albany"

Case "Peters, Mark","Baldwin, Anna",etc
cboLibraryLocation = "Buffalo"
etc for the remaining branches

End select

Allan
Click to expand...
 
That doesn't clarify your first question, did you resolve
it?

The key to most database issues is the table design and the
relationships between the tables so that kind of information
helps others understand the situation.

If I deduced some of the fields in the Processes table
correctly, I think(?) your second question can be dealt with
by using a little code in the main form's Current event:

If IsNull(DLookup("jobfinishedfield", "Processes", _
"JobID = " & Me.JobId)) Then
Me.cmdProcesses.ForeColor = vbRed
Me.cmdProcesses.Bold = True
Else
Me.cmdProcesses.ForeColor = vbBlack
Me.cmdProcesses.Bold = False
End If
--
Marsh
MVP [MS Access]

The error was probably that I identified it as a subform. It is actually
just a separate form.

To explain further - my application is a workflow tracking system.

The Main form is used by word processors to track the jobs they work on.
Each job is given a job number (that record's autonumber), and pertinent
information is entered in the Main form about the job (Requestor, branch
office, document name, network number, client code, etc.).

In order to sign on and off of a job, the word processor clicks the
cmdProcesses button (caption is "Operator"). When the word processor clicks
the button, the Processes form opens, and it has two text boxes - one to
enter the date/time they started the job, and one to enter the date/time they
finished the job.

In the OnClick event of cmdProcesses is the following code:

-------------------------------------------------------------------
stLinkCriteria = "JobId = " & Me.JobId
stDocName = "frmProcesses"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog [snip]
-------------------------------------------------------------------

The Record Source for frmProcesses is the Processes table, which is linked
to the Employees table in a one (Employees) to many (Processes) relationship
via the EmpId field.

This needs to be clarified (a subform is not "opened"). If
it truely is a subform, then I do not understand what
purpose the button serves.

If it is a separate, albeit related form, then the button's
color depends on the data in the table that you want to
display in the processes form. This can be done with a
DLookup function, but it depends on how the two tables are
related.
Click to expand...
Click to expand...
 
Hi Marsh,

Thank you so much. I will remember. Yes, the first question was resolved.

Thanks again,
Rosemary


Marshall Barton said:
That doesn't clarify your first question, did you resolve
it?

The key to most database issues is the table design and the
relationships between the tables so that kind of information
helps others understand the situation.

If I deduced some of the fields in the Processes table
correctly, I think(?) your second question can be dealt with
by using a little code in the main form's Current event:

If IsNull(DLookup("jobfinishedfield", "Processes", _
"JobID = " & Me.JobId)) Then
Me.cmdProcesses.ForeColor = vbRed
Me.cmdProcesses.Bold = True
Else
Me.cmdProcesses.ForeColor = vbBlack
Me.cmdProcesses.Bold = False
End If
--
Marsh
MVP [MS Access]

The error was probably that I identified it as a subform. It is actually
just a separate form.

To explain further - my application is a workflow tracking system.

The Main form is used by word processors to track the jobs they work on.
Each job is given a job number (that record's autonumber), and pertinent
information is entered in the Main form about the job (Requestor, branch
office, document name, network number, client code, etc.).

In order to sign on and off of a job, the word processor clicks the
cmdProcesses button (caption is "Operator"). When the word processor clicks
the button, the Processes form opens, and it has two text boxes - one to
enter the date/time they started the job, and one to enter the date/time they
finished the job.

In the OnClick event of cmdProcesses is the following code:

-------------------------------------------------------------------
stLinkCriteria = "JobId = " & Me.JobId
stDocName = "frmProcesses"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog [snip]
-------------------------------------------------------------------

The Record Source for frmProcesses is the Processes table, which is linked
to the Employees table in a one (Employees) to many (Processes) relationship
via the EmpId field.

This needs to be clarified (a subform is not "opened"). If
it truely is a subform, then I do not understand what
purpose the button serves.

If it is a separate, albeit related form, then the button's
color depends on the data in the table that you want to
display in the processes form. This can be done with a
DLookup function, but it depends on how the two tables are
related.
Click to expand...
Question 2:

There is a command button control named cmdProcesses, which is set in the
header section of the Main form, named frmMain. Clicking cmdProcesses opens
a subform named frmProcesses. In this subform there are two text boxes,
named txtStartDateTime and txtEndDateTime.

If txtStartDateTime is filled in, but txtEndDateTime is empty, it means a
job is in progress. I want the command button to reflect whether a job is in
progress or not by its color.

So, if txtStartDateTime is filled in (e.g., 3/2/2008, 5:55:00PM) but
txtEndDateTime is empty, I want either the forecolor or the backcolor of
cmdProcessses to change to red (or some other property that makes it stand
out).
Click to expand...
Click to expand...
Click to expand...
 
Hi Allan - Sorry, one quick question,

The RequestorName strings are actually longer than just the employee's name.
They're actually like:

Case "Johnson, Michael, Intellectual Property, Floor 19, ext. 2123, Emp No.
2323","Price, Cynthia, Trusts and Estates, Floor 18, ext. 2124, Emp. No.
1212", etc.
cboLibraryLocation = "Albany"

If I want to just type in the employee name without all the rest of the
info., can I use a wildcard character for the remainder of the string, like:

"Johnson, Michael*",Price, Cynthia*",etc.

Thanks again!
Rosemary
 
Back
Top