maintaining User id across forms

[Hadavidi]

The db I am trying to work on is for a small clinical study. My knowledge of
using VBA is nil to non-existent. I am trying to learn as I go. So, pls bear
with me. I have posted this on the access.forms forum also.

As I see it there are multiple issues I am dealing with:

Let's say there is a demographic data entry form. Once the user enters the
demo info for a participant and saves this info, a prompt should pop up
asking if the user would like to enter medication info for this patient.

=> If the user chooses yes, then it should take them to the medication data
entry form. Assuming they enter this info and save it, it should prompt them
and ask if they would like to enter diagnosis info.

=> Across all these forms, how do I make sure that I have participant ID
from the demographics form.

=> Another issue is, for a given participant, there may be more than one
diagnosis or medication that they are on. On the form, I am planning to
display a list box from which the user can choose. Once the user chooses
either multiple medications or diagnosis, how do I do a multiple update.
Ahead of time, its hard to know how many medications or diagnoses a given
participant may have.

Any pointers or hints would be greatly appreciated especially with adding
the code behind the form.

Thx
Hadavidi

 

[Scott Vincent]

Let's see now. There are a couple of ways to do this.

Option 1: Global Variable
Insert a module into the Access database and add a variable declaration as
follows:

Global lngParticipant As Long

Now go to the AfterUpdate code behind the combo box you use to select the
participant and set this variable to the value of the combo box. Of course,
this assumes you have the ID set as the bound column.

Private Sub cboParticipant_AfterUpdate()
lngParticipant = me.cboParticipant
End Sub

Global variables are available to all forms and all modules. They hold
their value until the code is reset or you change them.

Option 2: Hide the form rather than closing it.
Do not close the participant form when you open these other forms. You can
hide a form by putting this in instead of the close statement:

Me.Visible = False

With the form hidden, you can refer to the combo box from other forms:

forms("frmParticipantInfo").cboParticipant

If you use this option, you will have to remember to close the participant
form at some point. Sometimes I find it easier to hide the main form then
open the other forms as dialog boxes, which stops the code, then close the
main form when everything is done.

Option 3: Use the openargs parameter
Access Help
OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode,
OpenArgs)

Pass the value of the participant ID as the OpenArgs parameter when you open
one of the other forms. In the Load event of the other form, do the
following:

Private Sub Form_Load()
Dim lngParticipantID
lngParticipantID = Me.OpenArgs
End Sub


There are a couple of options for the second issue as well.
Getting the results from a multiple selection listbox is not simple code. I
can tell you how to do that, but first I would ask how you are saving this
information.

If you use a table, say tblMedications, that has ParticipantID and
MedicationID, then you might just want to make a subform that has a combobox
and is linked on the participant form so you can select as many medications
as the participant has. You can do the same for Diagnosis.

Let me know if you need any further assistance.

Scott