Multiple Data Fields from a Combo Box 3

[mpnorris]

I am new to using Access with a little VB experience.

I have created a form where I am choosing a user from a combobox. The combobox is being populated by a query. After choosing a user name I am trying to carry over two of the data fields (User ID and Department) that appear in the combobox to another form when clicking the "Continue" button. The continue button is to open another form and pre-populate two text boxes on the new form.

If anyone has any suggestions or sample code to view I would appreciate any response.

 

[FirstAndGoal4]

Use the OpenForm method and pass the value in the OpenArgs parameter. In the OnOpen event of the 2nd form, get the values from Me.OpenArgs and load into your controls.

 

[mpnorris]

Do you have an example of this? I am unfamiliar with the OpenArgs parameters. Can you explain in a little more detail? Sorry, I am not a programmer and some of this stuff is a little over my head. I appreciate your quick response and any help you can give me is greatly appreciated.

Thanks again

 

[SBendBuckeye]

You can pass multiple fields to a combobox via a query. The trick is to set the width to zero so the combobox does not display them. You can then reference them via the combobox columns individually.

As an example, say you had the following tblUserData with these fields:

Key
FirstName
LastName
UserID
Department

Then you could populate your combobox with a query such as the following:

SELECT LastName & ", " & FirstName AS UserName, UserID,
Department
FROM tlbUserData
ORDER BY LastName, FirstName, UserID, Department

Set the row widths property to 1;0;0; to suppress the last 2 columns from displaying in the combobox. Then when you want to open the newform with the selected data, you do the following (columns are zero based). The last parameter for the openform command is OpenArgs:

DoCmd.OpenForm "FormName",,etc,,YourComboBoxName.column(1) _
& "~" & YourComboBoxName.column(2)

Then in "FormName" put code like this in the Form Load or Form Open event:

Dim intUserID As Integer
Dim strDepartment As String
Dim intTilde As Integer

If Not IsNull(OpenArgs) Then
intTilde = InStr(OpenArgs, "~&quot
intUserID = Val(Trim$(Left$(OpenArgs, intTilde - 1)))
strDepartment = Trim$(Mid$(OpenArgs, intTilde + 1))
End If

What the above code does is parse system variable OpenArgs based on the tilde ~ used as a separator by the calling program. InStr returns the position where it is found and then you can setup the 2 fields you passed in via OpenArgs.

Hope this helps! If you need some more help feel free to post again.

Happy New Year!

 

[FirstAndGoal4]

Open the "continue" form like this filling in any applicable parameters (filter, where condition, etc.):

DoCmd.OpenForm "FormName", acNormal, , , , acWindowNormal, "UserID|DeptID"

In the open event of the "continue" form:
Call SplitArgs(Me.OpenArgs, Me.UserId, Me.DeptId)

New procedure:
Private Sub SplitArgs(ByVal args As String, ByRef user As String, ByRef dept As String)
Dim pipePosition As Integer
pipePosition = InStr(1, args, "|&quot
user = Left(args, pipePosition - 1)
dept = Right(args, Len(args) - pipePosition)
Stop
End Sub

 

[mpnorris]

Thank both of you for your quick responses. I tried both options and I got errors both times. I am not sure why.

Here is the query I wrote to fill the combobox (cmbUser)on my first form (frmUserEntry):

SELECT Department.Dept_Name, Users.U_ID, Users.First_Name, Users.Last_Name, Users.Phone_Ext, Users.User_ID, Department.Dept_ID
FROM Department INNER JOIN Users ON Department.Dept_ID = Users.Dept_ID;

I am trying to pass Users.U_ID and Department.Dept_Name to the next form when I hit the "Continue" Button. I have put text box place holders on the next form (frmPaymentErrorEntry); txtUserID and txtDept to show the data. I then want to be able to reference that information within (frmPaymentErrorEntry) to enable and disable other fields and not have them reenter the information everytime they enter a transaction on (frmPaymentErrorEntry).

The Users table contains the following fields: User_ID (key), U_ID (Text), Dept_ID (Number), First_Name (text), Last_Name (text), Phone_Ext (text)

The Department Table contains the following fields: Dept_ID (key), Dept_Name (Text)

Hopefully this information is helpful. Any information that you could supply would be greatly appreciated. I am starting to believe I bit off more then I can chew with this little database project.

Thanks again.

 

[SBendBuckeye]

Let's break it down into a couple pieces to make it easier to discuss. Your SQL syntax to populate the ComboBox looks to be OK. I would add an ORDER BY clause so it comes into the ComboBox sorted as you want it. Below is an example.

SELECT Department.Dept_Name, Users.U_ID, Users.First_Name, Users.Last_Name, Users.Phone_Ext, Users.User_ID, Department.Dept_ID
FROM Department INNER JOIN Users ON Department.Dept_ID = Users.Dept_ID
ORDER BY Department.Dept_Name, Users.Last_Name, Users.First_Name;

Go into a new query design session and close the select table window without selecting anything. One of the options in the upper left is SQL view. Select that and past your SQL into that window and then run it. Does it run without errors? Do you get the results your were expecting?

If the above is OK you can save it as a query if you want to, but at least the SQL has been verified. Now that the SQL is known to be good, is your ComboBox displaying the way you want it to or expect it to? Do you really need all of the information in the query to just select a user? If not, cut it down to just the information you need.

Only one column out of your ComboBox can be the bound column. It looks like you would probably want UserID to be the bound column although in this case it probably doesn't make any difference (it is helpful if the bound column is numeric).

Once the user has selected someone in the ComboBox then the query information is available to you in the After Update code for the ComboBox. To set that up, click on the properties button for your ComboBox control while your form is open in design mode. Click on the Events tab and click to the right of the AfterUpdate line. Select event procedure and it will create a Sub header line and end sub for you. In that code you put something like the following:

If ComboBox > 0 Then 'Assuming you used UserID and nonZero
the various query fields used to populate the ComboBox
are available to you here as zero based columns - so for
instance in your SQL above, department name would be
referenced as ComboBox.Column(0) since it is first - the
UserID field would be ComboBox.Column(1) since it is
second or if it is the bound column you could also
reference it by the control name ComboBox.Value or
ComboBox.
End If

Are we OK to this point? If so, repost and we'll go on. Otherwise, post where things are breaking down for you and we'll start there.

Good Luck!

 

[mpnorris]

I got the SQL to work and the combobox has only the information that I need.

THe SQL I created:
SELECT Users.U_ID, Users.First_Name, Users.Last_Name, Users.Dept_ID, Department.Dept_Name
FROM Department INNER JOIN Users ON Department.Dept_ID = Users.Dept_ID
ORDER BY Users.First_Name, Users.Last_Name, Department.Dept_Name;

In the comboBox I have Department, FirstName and Last Name

When I created the combo box by using the wizard, it asked me which field I wanted associated with combo box and I chose Dept_Name. It only let me choose from the three fields I chose for the combo box view. Is this the only field I can bring over to the next form? From you previous posting it sounds like I can bring over all of the results from the query. That is when I became confused with your IF statement.

In your previous post you mentioned wanting UserID to be the bound column. How do I do that? If I don't want UserID in my comboBox how do I choose it to be the bound column? Am I confusing two different things?

Your IF statement looked like it was refering to if the choice in the ComboBox is greater then 0 (not empty) then what? What if it is not a number? HELP! Hopefully I am not confusing you and my uncertainty is clear.

I appreciate all your help. Anything you can do will be appreciated.

 

[SBendBuckeye]

Excellent, you are making good progress. Open your first form up in design view and click on your combobox to select it. Then click on the properties tool bar button so you can see the properties.

Click on the data tab and you will be able to set things up the way you want. ControlSource should be blank. RowSourceType shoule be Table/Query. In the RowSource either put in your query name or actually paste your SQL there, which ever is easier.

The bound column is a 1 based (NOT zero based like most things in Access) column from your SQL. I always set my SQL up so the bound column is in column 1, that way I never have to think about it.

LimitToList restricts the user to ONLY those items currently in the list while AutoExpand moves through the list as you key (eg if you typed w and your bound column was last name it would take you to the first last name beginning with W, wi to first wi in last name, etc).

On the event tab select [Event Procedure] for the AfterUpdate event. We'll talk more on that later.

On the format tab, set ColumnCount to the total number of columns in your SQL. ColumnWidths will take a comma delimited list which matches to the ColumnCount. If you want the combobox to hide a column from the user, set that column width to 0. Say you had 4 columns and you only wanted the user to see column 2, the others were for other processing similar to the types of things you are doing. In that case, ColumnWidths would be 0,1,0,0 assuming you wanted your combobox to drop down a 1 inch wide list.

Another example of the above would be if you needed to use a DeptID for your processing but wanted the user to be able to select from the DeptName. In that case you would do something like the following and set the DeptID width to 0 so the user only saw the names, but the system could retrieve the DeptID for processing:

Select DeptID, DeptName From Department Order By DeptName

In the AfterUpdate code the system creates for you, you would then have access to every column in your SQL via the combobox. Next will be passing that information to your second form.

In your second form, will you be adding only 1 record for each user or multiple records? Will you be using a lot of information from the User table or only the DeptID and UserID? If you can give a little background on how you will be using the second form it will be helpful.

Keep up the good work! Good LucK!

 

[mpnorris]

So Far so good. I am with you up to this point. to answer your questions -

My second form is my main data entry form. On my second form I will be having each user enter multiple transactions. My hope was to bring in the U_ID (text) and/or First_Name, Last_Name (Haven't made up my mind yet) and the Dept_ID and/or Dept_Name. I only need the Dept_ID or Dept_Name to work the enabling or disabling of other data entry fields. I was thinking of potentially displaying the U_ID/Name and Dept_Name so that the user knows it is their session they are working on. I was hoping to run reports based on Dept and user, so I wanted to associate the persons name and department with each transaction.

So to make a long story short, the Dept_ID is the only crucial bit of data, other things rely on it (enable/disable), but some of the other data could be useful from a user experience view.

Once again your help has been invaluable. I think we are making progress. I think we are at the IF stament from your previous post. I am sensing that we will be working on the passing of data from form 1 to form 2. I also feel I need explanation of the DoCmd.Open Form command, especially with the ~. I don't know if I am jumping ahead, but I just thought I would give a heads up. thanks again.

 

[SBendBuckeye]

Excellent again. Now its time to make some decisions. It looks to me like the information you are going to pass is all static information based on the user. As such, you can pass it or you could also get most or all of it in form 2.

Also, it just occurred to me that you are really just trying to get the user. I modified this a while ago from Access Envir function. If you run the following code and look at the output, your UserID may already be there and they won't have to select themselves.

Public Sub GetEnvironment()
Dim strEnvironment As String
Dim intX As Integer
Dim intPathLength As Integer
intX = 1
Do
strEnvironment = Environ(intX) 'Get env variable
' MsgBox strEnvironment
Debug.Print strEnvironment
intX = intX + 1
Loop Until strEnvironment = vbNullString
End Sub

But let's carryon for now because this is a great learning opportunity for you. When you call another program in Access you can pass it information directly via a system variable named OpenArgs. You are responsible for the data on both ends. What I did earlier was create a string which was delimited by a tilde ~ so that I could then parse it in form 2.

So, in the After Update event code above, you can create a string from the selected combobox and pass it to form2. If you use the & concatenation operator it will take care of numerics and strings for you. Since we are in After Update code we don't really need the check so let's discard it.

Here is the SQL for reference purposes.

SELECT Users.U_ID, Users.First_Name, Users.Last_Name, Users.Dept_ID, Department.Dept_Name

Dim strOpenArgs As String
strOpenArgs = ComboBoxName.Column(0) & "~" & _
ComboBoxName.Column(1) & "~" & _
ComboBoxName.Column(2) & "~" & _
ComboBoxName.Column(3) & "~" & _
ComboBoxName.Column(4) & "~"

Now pass it to form2 as the last parameter of the DoCmd.OpenForm statement. If you want form1 to wait until form2 is finished use acDialog when you call form2. So it will look like this:

DoCmd.OpenForm "Form2Name",,etc,,strOpenArgs

Now in form2, we parse the OpenArgs string we sent from form1. You do this in the Form Load code using the Access variable named OpenArgs.

dim strOpenArgs As String
dim strTemp As String
dim intPos As Integer
dim intCounter As Integer

If Not IsNull(OpenArgs) Then
Do While Len(strOpenArgs) > 0
intPos = InStr(strOpenArgs, "~&quot 'Find first ~
strTemp = Left$(strOpenArgs, intPos - 1)
strOpenArgs = Mid$(strOpenArgs, intPos + 1)
Select Case intCounter
Case 0 'U_ID
Form2.UserIDControlName = strTemp
Case 1 'First_Name
Form2.FirstNameControlName = strTemp
Case 2 'Last_Name
Form2.LastNameControlName = strTemp
Case 3 'Dept_ID
Form2.DeptIDControlName = strTemp
Case 4 'Dept_Name
Form2.DeptNameControlName = strTemp
End Select
intCounter = intCounter + 1
Loop
End If

If you need the data but don't want the users to see it all just set the control's visible property to false. Also, if you want these to be read only to the user, set enabled to false and locked to true. This will make it impossible for the user to even click in the control.

Another way to do this would be to get the information in form2 based on say just the U_ID passed in from form1. To do that, you would use the Domain Aggregate Functions like this assuming you had the U_ID in an integer variable.

General DLookUp = ("FieldName", "TableName", "Criteria = " _
& VariableName)

Dim strTemp As String
strTemp = DLookUp("First_Name", "Users", _
"U_ID = " & intU_ID)

If U_ID was a string you would need to surroun it with single quotes like so: "U_ID = '" & strU_ID & "'&quot. If it were a date you would surround it with pound signs like so:
"U_ID = #" & datU_ID & "#&quot.

Good Luck and Have a Great Day! Hope you're having as much fun with this exercise as I am.

 

[mpnorris]

I am trying to write the After Update code that you sent me and I am getting compiling errors while I am writing it.
This is exactly as I wrote it below. I am being prompted that the _ is an invalid character. What am I missing. Do I need to reference back to one of your previous posts. I don't know if I missed some code from a previous post or had we started over from Jan 2?

Dim strOpenArgs As String
strOpenArgs = cmbUser.Column(0) & "~" &_
cmbUser.Column(1) & "~" &_
cmbUser.Column(2) & "~" &_
cmbUser.Column(3) & "~" &_
cmbUser.Column(4) & "~" &_

Also I am confused with the first part of your last post. The code you have there, where and how would you run it and where is it getting the UserID from? Would it be getting it from the initial network login?

And does the first part have anything to do with the code above? I thought it was a separate scenerio and that they are not dependant on the other but I thought I would ask.

Somewhat confused that I may have missed something along the way. I have been doing it step by step from yesterday as a clean slate.

Any help is appreciated.

 

[SBendBuckeye]

You need a space between the & and the _. The underscore _ is a line continuation indicator.

Sorry to confuse you. The extra code for sub GetEnvironment with the Environ variable has nothing to do with the rest of the thread. If you paste that code into a standard code module, you can invoke it from the immediate window by typeing in GetEnvironment and pressing enter. It will then list to the immediate window your environment variables.

You can do the same thing in a DOS window by typeing SET and pressing enter. You may find that U_ID is already present in a different form that you can use without prompting the user for it. For instance on my system, my logonid is present in an environment variable named UserName.

It appears that possibly the only thing you are retrieving from form1 is who the current user is. If so, this is another possible way to do it without user intervention. What you have will work just fine.

Good Luck!

 

[mpnorris]

So far so good. I made the code changes and I get the 2nd form open, but I am not sure if the data from form 1 is coming over. I used the control names which are on the form in the Case statement, but nothing appears on the form. How do I do that? My first thought was to see all of the data and then choose what I need to see and what I don't.

Here is the code I entered in Form_Load:
Private Sub Form_Load()

Dim strOpenArgs As String
Dim strTemp As String
Dim intPos As Integer
Dim intCounter As Integer

If Not IsNull(OpenArgs) Then
Do While Len(strOpenArgs) > 0
intPos = InStr(strOpenArgs, "~&quot 'Find first ~
strTemp = Left$(strOpenArgs, intPos + 1)
Select Case intCounter
Case 0 'U-ID
Form_frmPaymentErrorEntry.txtUserID = strTemp
Case 1 'First_Name
Form_frmPaymentErrorEntry.txtFirstName = strTemp
Case 2 'Last_Name
Form_frmPaymentErrorEntry.txtLastName = strTemp
Case 3 'Dept_ID
Form_frmPaymentErrorEntry.txtDeptID = strTemp
Case 4 'Dept_Name
Form_frmPaymentErrorEntry.txtDeptName = strTemp
End Select
intCounter = intCounter + 1
Loop
End If
' Enabled the Activity dropdown if the users department is "Account Research"
If Me!txtDeptID = 1 Then
Me!Activity_ID.Enabled = True
Else
Me!Activity_ID.Enabled = False
End If
End Sub

The text boxes txt.UserID, txtFirstName, txtDeptID, txtDeptName are all visible on the form. I would like the data that is brought over to the form to show so that:
1. I can verify the information and 2 make sure that if I need to referemce something I am doing it correctly. The bottom portion of my Form_Load is what I need this whole step for. Activity data only needs to be entered if the user is from Account Research. IF they are not, an activity does not need to be chosen.

Currently each of these text boxes say "Unbound" in them.

Something also seems wrong with my strOpenArgs statement. In code mode the entire strOpenArgs statement is red, except for the _. I am sensing that may be why the data is not coming over. What do you think. cmbUser is what I called the combobox.

Private Sub cmbUsers_AfterUpdate()

Dim strOpenArgs As String
strOpenArgs = cmbUser.Column(0) & "~" & _
cmbUser.Column(1) & "~" & _
cmbUser.Column(2) & "~" & _
cmbUser.Column(3) & "~" & _
cmbUser.Column(4) & "~" & _
End Sub

This is the code I put for when you click the continue button.

Private Sub Continue_Click()
DoCmd.OpenForm "frmPaymentErrorEntry", , etc, , strOpenArgs
End Sub

What am I missing? Let me know what you think. I hope I am not becoming a pain. This has been great. I am learning alot.

 

[SBendBuckeye]

Sorry, my bad! I thought you were opening form2 as soon as the user selected selected something from the combobox (bad memory which is why I shouldn't rely on it).

Since you used a dim statement in your combobox After Update code, it will only exist while that code is being executed. That is called local scope. The variable is created when the code runs and vanishes as soon as the code finishes. So that means it is gone by the time you get to your click event code.

As an aside, if you put Option Explicit in the Declarations Section (at the very top of the module) you will get an error if you try to reference a variable which has not been explicitly dimensioned before you use it.

For this case, instead of using Dim ect in your After Update code, remove that line and replace it with the following in the Declarations Section which will create it as a module level variable (eg visible while the module is running)

Private mstrOpenArgs As String

Notice that I changed it to mstr - that is how I show that it is a module level variable as opposed to a local variable. Now when you create it in After Update code it will still be accessible when you need it for your click event code.

If it isn't working quite right, break it down into just one piece (eg comboboxname.column(0)) and once that works than add the second piece, etc.

Sorry, when I put etc in the DoCmd.OpenForm I thought you would know to add as many commas as you need to get to the OpenArgs part. After you type in DoCmd.OpenForm and type a space intellisense should activate so you can see what options you have. It should be as below to take all defaults:

DoCmd.OpenForm "Form2Name", , , , , , mstrOpenArgs

As far as form2 goes, will this information be saved as part of the new records being created? If the answer is yes, then we need to change things a little bit. If the answer is yes, you want the controls on form2 to be bound so that the database is updated as changes are made.

An easy way to do that is to create as many module level variables as you need when you parse OpenArgs. In the Declarations portion create variables such as:

Private mstrFirstName As String
Private mintUserID As Integer, etc

Then just setup these variables instead of strTemp as we did in the earlier example. As an aside, any object always exposes a reference to itself called me. So instead of this code: Form_frmPaymentErrorEntry.txtUserID = strTemp you can use: me.txtUserID = strTemp.

Once you have setup the variables, then you set your textboxes in form2 equal to those values during Form Current processing. On Current processing occurs every time you go to a different record, add a new record, etc. So you would put code like so to set things up:

me.txtUserID = mintUserID

Access is smart enough to do the numeric conversion for you since you are using a textbox. If I threw too much at you and lost you along the way, let me know.

Have a great day!

 

[mpnorris]

I just changed the DIM statement to the module level variable and I still get a compile error saying it is expecting an expression.

Here is what I changed the After Update to:

Private Sub cmbUsers_AfterUpdate()
'Dim strOpenArgs As String

Private mstrOpenArgs As String
mstrOpenArgs = cmbUser.Column(0) & "~" & _
cmbUser.Column(1) & "~" & _
cmbUser.Column(2) & "~" & _
cmbUser.Column(3) & "~" & _
cmbUser.Column(4) & "~" & _


End Sub

The answer to your question about information being sent to form 2 being saved as part of the record, the answer is yes. Some of the data will be saved. I think I know what you are talking about with declaring those variables. I will work on that until I hear back about the compile error. I am beginning to understand this a little better.

 

[SBendBuckeye]

This code must go at the very top of your module. By definition, module level variables have to be put in the definitions section (eg about line 3 or 4).

Option Compare Database
Option Explicit

Private mstrOpenArgs As String '<<== NOTE

FirstSub or Function, etc.

Then in the code you just reference it like normal because it has already been dimensioned above.

Don't overlook your public library as a resource. Our's has a very nice selection and I learned a lot initially by reading books from the library. Especially look for any that come with an accompanying CD with code. Nothing helps the learning like kicking code into single step mode and watching what happens as it runs.

Good LucK!

PS. I am getting close to being gone for the day most likely. I'll try to check later in the evening if I can but once the Fiesta Bowl starts I'm history.



Private mstrOpenArgs As String

 

[mpnorris]

One quick question for you, in the Do While loop, before I change everything in the Case statements to specific variables, the second line:

strTemp = Left$(strOpenArgs, intPos-1)

1 - What variable do I set this to? I am changing strTemp to more specific variables. Do I set it to the variable in Case 0?

2 - Since we are making a change to strOpenArgs (to mstrOpenArgs), I am assuming that change has to be made wherever it was referenced before? I just want to verifiy this.

so the above line would be: strTemp = Left$(mstrOpenArgs, intPos - 1)

Hopefully you get this before you leave. I am going on vacation myself tomorrow until next Wendsday. After today I will pick this up next week. Thank you so much for your patience. Enjoy the game.

 

[mpnorris]

Also, does the order of the Case Statements matter? I wanted to add another field from the Query and I wanted to check to see if the order mattered.

Thanks.

 

[mpnorris]

I still have an issue that seems to be causing problems. I ran through the forms and I am getting to the second form but the data is not filling the text boxes. Even though I made the change to a module level variable on my first form, the mstrOpenArgs statement is still red. To me this looks as if something is wrong.

Below is all of the code for form 1 - frmUserEntry:

Option Compare Database
Option Explicit

Private mstrOpenArgs As String '

Private Sub User_AfterUpdate()



End Sub

Private Sub User_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Close_Click()
On Error GoTo Err_Close_Click
DoCmd.Close
Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub


Private Sub cmbUsers_AfterUpdate()

mstrOpenArgs = cmbUser.Column(0) & &quot;~&quot; & _
cmbUser.Column(1) & &quot;~&quot; & _
cmbUser.Column(2) & &quot;~&quot; & _
cmbUser.Column(3) & &quot;~&quot; & _
cmbUser.Column(4) & &quot;~&quot; & _


End Sub

Private Sub Continue_Click()

DoCmd.OpenForm &quot;frmPaymentErrorEntry&quot;, , , , , , mstrOpenArgs

End Sub

Private Sub Detail_Click()

End Sub

Private Sub Form_Load()

End Sub

Private Sub Form_Open(Cancel As Integer)

End Sub

Here is the code for my second form frmPaymentError Entry:

Option Compare Database
Option Explicit
Private mstrOpenArgs As String
Private mstrUID As String
Private mintUserID As Integer
Private mintDeptID As Integer
Private mstrDeptName As String
Private mstrFirstName As String
Private mstrLastName As String
Private mintPos As Integer
Private mintCounter As Integer
Private mstrTemp As String

Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec



Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub
Private Sub Refresh_Click()
On Error GoTo Err_Refresh_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70



Exit_Refresh_Click:
Exit Sub

Err_Refresh_Click:
MsgBox Err.Description
Resume Exit_Refresh_Click

End Sub



Private Sub ErrorID_AfterUpdate()
' Disable the Correct_Acct_Num text box if the user selected &quot;Mispost&quot; Error type.
'

Const conMispost = 1
Const conEncode = 4


If Me![Error_ID] = conMispost Then
Me![Correct_Acct_Num].Enabled = True
Else
Me![Correct_Acct_Num].Enabled = False
End If

If Me![Error_ID] = conEncode Then
Me![Correct_Amt].Enabled = True
Else
Me![Correct_Amt].Enabled = False

End If


End Sub

Private Sub Form_Current()
Me.txtUser_ID = mintUserID
End Sub

Private Sub Form_Load()

If Not IsNull(OpenArgs) Then
Do While Len(mstrOpenArgs) > 0
mintPos = InStr(mstrOpenArgs, &quot;~&quot 'Find first ~
mstrTemp = Left$(mstrOpenArgs, mintPos + 1)
Select Case mintCounter
Case 0 'U-ID
Me.txtU_ID = mstrUID
Case 1 'First_Name
Me.txtFirstName = mstrFirstName
Case 2 'Last_Name
Me.txtLastName = mstrLastName
Case 3 'Dept_ID
Me.txtDeptID = mintDeptID
Case 4 'Dept_Name
Me.txtDeptName = mstrDeptName
Case 5 'User_ID
Me.txtUser_ID = mintUserID
End Select
mintCounter = mintCounter + 1
Loop
End If
' Enabled the Activity dropdown if the users department is &quot;Account Research&quot;
If Me.txtDeptID = &quot;1&quot; Then
Me.Activity_ID.Enabled = True
Else
Me.Activity_ID.Enabled = False
End If
End Sub

Private Sub Form_LostFocus()

End Sub

Private Sub Form_Open(Cancel As Integer)
dataEntry = True

Me![Correct_Acct_Num].Enabled = False
Me![Correct_Amt].Enabled = False
Me![Activity_ID].Enabled = False

End Sub

Private Sub Close_Form_Click()
On Error GoTo Err_Close_Form_Click
DoCmd.Close
Exit_Close_Form_Click:
Exit Sub

Err_Close_Form_Click:
MsgBox Err.Description
Resume Exit_Close_Form_Click
End Sub

Private Sub ProductID_BeforeUpdate(Cancel As Integer)
End Sub

Private Sub UserID_AfterUpdate()
End Sub

Private Sub UserID_BeforeUpdate(Cancel As Integer)
End Sub

Please let me know what you think. I will check in later or when I get back. Thank you sooo much for your help. We are making progress.