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.

 

[SBendBuckeye]

I think you are confusing our form1 and form2 discussions.

In form1 you used a module level mstrOpenArgs because you created the string in After Update event code but needed to reference it in the Click event code when you actually open up and call form2.

In form2 you want to continue to reference strTemp and strOpenArgs because all you are doing is assigning a local variable to OpenArgs so you can parse it. The change comes in your select case code. Instead of setting your form2 textboxes to strTemp you will now be setting your module level variables to strTemp. The only thing that changes in form2 load processing is the following:

OLD Case 0 'U-ID
Form_frmPaymentErrorEntry.txtUserID = strTemp

New Case 0 'U-ID
mstrU_ID = strTemp

This is assuming you set up as many module level variables as you need in form2 in the Declarations section of your code.

The order of the case statement is CRITICAL. It must match the order that you used when you created a concatenated string in form1 of all of the information you wanted to pass to form2 via OpenArgs. If you need to add something new or change things around, that's fine as long as you maintain the same order in form1 and form2. If you have U_ID, FirstName, LastName as the concatenation order in form1 then your select case statement in form2 must be in U_ID, FirstName, LastName sequence as well so they match.

After you have parsed OpenArgs in form2, just look at them and ensure that you have mapped everything correctly.

Good Luck!

 

[mpnorris]

Thank you for the reply. Since I am not atwork right now I can't make any changes. There is one question I have, the AfterUpdate code doesn't seem to be correct. It is red when I write it. If I remember correctly, red isn't good. I don't know if some of the other things we have discussed and needs to be changed will effect this but I thought I would ask.

Private Sub cmbUsers_AfterUpdate()

mstrOpenArgs = cmbUser.Column(0) & "~" & _
cmbUser.Column(1) & "~" & _
cmbUser.Column(2) & "~" & _
cmbUser.Column(3) & "~" & _
cmbUser.Column(4) & "~" & _
End Sub

I am off on vacation, if the weather clears up. I will be in contact next week. Thank you for everything.

 

[SBendBuckeye]

I have been swamped. Repost when you get back from vacation and we'll pick this up again.

Happy New Year!

 

[SBendBuckeye]

Regarding your last question, get rid of the last & _. It is trying to do a continuation into the End Sub statement.

Have a great day!

 

[mpnorris]

Thank you for all of your help but I still seem to be having problems. I can get the second form to open but once again, the data doesn't go into the text boxes. I think I am confused as to what exactly is the process in the code that I have been working with you on. I thought I understood that on the first form, when I choose the user from the dropdown box, the information from the query for that user is held. When I click the "Continue" button the next form is opened and the data is supposed to be visible in the text boxes that I have put onto the form. I am sensing that this is where I have the issue.

From your last post it seems that the Case statement is where I bring over the data from Form 1 to Form 2. According to your info I put the data into the new module level variables that I set up. Am I missing a step to have the text boxes on the form refernce the new variables that were declared? I am beginning to think that I am going in circles. Below is the code for the two forms at this point. What am I missing?

Form 1:

Option Compare Database
Option Explicit

Private mstrOpenArgs As String '


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) & "~" & _
cmbUser.Column(1) & "~" & _
cmbUser.Column(2) & "~" & _
cmbUser.Column(3) & "~" & _
cmbUser.Column(4) & "~" & _
cmbUser.Column(5) & "~"

'Column(0) = User_ID, Column(1) = U_ID, Column(2) = Dept_ID, Column(3) = Dept_Name, Column (4) = First_Name, Column(5) = Last_Name

End Sub

Private Sub Continue_Click()

DoCmd.OpenForm "frmPaymentErrorEntry", , , , , , mstrOpenArgs

End Sub

Form 2:

Option Compare Database
Option Explicit
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 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 "Mispost" 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_Load()
Dim strOpenArgs As String
Dim intPos As Integer
Dim intCounter As Integer
Dim strTemp As String

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 'User_ID
mintUserID = strTemp
Case 1 'U_ID
mstrUID = strTemp
Case 2 'Dept_ID
mintDeptID = strTemp
Case 3 'Dept_Name
mstrDeptName = strTemp
Case 4 'First_Name
mstrFirstName = strTemp
Case 5 'Last_Name
mstrLastName = 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

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

 

[SBendBuckeye]

A couple questions for you. If you kick your code into single step mode, what happens at the following places? Let's try to narrow things down a bit as your code appears to be OK as far as I can tell by desk checking it. If you don't want to mess with single step mode put the following at the end of your AfterUpdate code and before the form2 call in the ClickEvent code:

msgbox mstrOpenArgs & " in AfterUpdate or whatever"

A. Is mstrOpenArgs being build correctly by AfterUpdate code? It should look something like the following:
"JDow~U_ID~R322~Sales~John~Doe~"

B. If A above is correct, do you have the same value being displayed just before you call form2?

C. If A and B are correct, do you have the same value being displayed in form2 when you check OpenArgs (or msgbox OpenArgs)?

Let me know and Good Luck!

 

[SBendBuckeye]

Aarrrghh. You need the following line of code in your form2 load code. Without that line, the length of strOpenArgs is zero and the code never gets run. Sorry I missed it earlier.
If you kick your code into single step mode before you make the change you can watch it skip it entirely.


If Not IsNull(OpenArgs) Then
strOpenArgs = OpenArgs <<<<====== Add this line
Do While Len(strOpenArgs) > 0

Good LucK!

 

[mpnorris]

Sorry, but how do you initiate single step mode? This is the first time I heard of this. Thanks.

 

[mpnorris]

I put the msgBox code into the two spots that you indicated. It seems that the mstrOpenArgs is not working correctly or I didn't enter your message box code correctly because I only got the response &quot;In AfterUpdate or whatever&quot; in a message box after I hit the continue button on the first form. When I chose the user from the dropdown box nothing happened. I needed to hit the button before I received a message box. It did not have any of the user data in the message box. Let me know what you think. Thanks.

Here is where I added the new code on form 1:

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; & _
cmbUser.Column(5) & &quot;~&quot;
'Column(0) = User_ID, Column(1) = U_ID, Column(2) = Dept_ID, Column(3) = Dept_Name, Column (4) = First_Name, Column(5) = Last_Name

MsgBox mstrOpenArgs & &quot;in AfterUpdate or whatever&quot;

End Sub

Private Sub Continue_Click()

MsgBox mstrOpenArgs & &quot;in AfterUpdate or Whatever&quot;

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

End Sub

 

[SBendBuckeye]

There are three ways to do it simply when you have a module open in code view. The easiest way is to just click in the gray bar to the left of your code. A brown dot will show up at that line where you have created a breakpoint. Click it again and it will go away. You can do the same thing by toggling break point off of the Debug menu or by clicking the hand on the tool bar. In the latter cases, it will set it at the line where your insertion pointer is at. You cannot breakpoint at Dim statements.

Once you have set a breakpoint, then run your code like normal and it will stop at your breakpoint and the line will be yellow. You can press F8 to single step through your code or once you have seen what you need to see press F5 and it will take off and run again.

While the code is paused, you can check out variable values in the immediate window locals pane or by choosing view locals off of the debug menu while your code is running. If you are running Access97, being in breakmode before executing some commands (such as Save, etc) will cause an error.

I'm sorry about missing that line of code in your form2 load event code, but it is a good lesson. Do things in steps and it will help keep things from being totally overwhelming sometimes.

For instance, if the string is being built properly in the AfterUpdate code, you can eliminate one place to look, etc.
Also, if it is now running but appears to go away after the first record has been added, you may need to refresh your textboxes on form2 in the OnCurrent event code.

As a small aside, you have code in form2 open and load events. Most of the time, you can do it all in one place to make keeping track of things a little bit easier.

Good Luck!

 

[mpnorris]

Something seems to be wrong with the initial taking the data from the comboBox. I don't get anything when I run the code one step at a time and therefore it doesn't flow through.

I don't get any msg box after the after_update process. Is there any way I could send you the database as is and see if something outside of the code could be causing problems?

Perhaps it would be easier if you had access to the complete package so that we could cover more ground at a quicker pace. I am getting a little pressure here to have this up and running soon. I know you are helping greatly and I am learning, but I am getting the feeling that my manager doesn't feel I am learninig quickly enough.

I appreciate all of your help and anything you can do is greatly appreciated.

Thanks again.

 

[SBendBuckeye]

you can send a zipped file to SBendBuckeye2000@yahoo.com. Post again when you have sent it and I'll watch for it. Make sure you have included any tables and queries the forms reference. A few records out of each should suffice.

Good LUck!

 

[SBendBuckeye]

From a little test one I did:

Combobox properties:

Format Tab - ColumnWidths - 0&quot;;0.75&quot;;0.75&quot; '<== Hides Col 0
ColumnCount - 3 '<== Number Cols

Data Tab - BoundColumn - 1 '<== 1st One
RowSourceType - Table/Query
ControlSource - '<== Leave Blank
RowSource - See Below

select keyid, fname, lname from tblFamily

After Update Code for control named ComboBox17:

Private mstrCombo17 As String 'In Declarations Section

Private Sub Combo17_AfterUpdate()
mstrCombo17 = Combo17.Column(0) & &quot; &quot; & _
Combo17.Column(1) & &quot; &quot; & Combo17.Column(2)
MsgBox mstrCombo17
End Sub

Good Luck!

 

[mpnorris]

I emailed you a copy of my file. Let me know what you think.

 

[SBendBuckeye]

You were almost there. I found a few things, a couple of which were on me.

1. In form1, your combobox name is singular and your After Update code is plural. Go back into the combobox properties and reclick on AfterUpdate on the Event tab. It will create another block of code. Copy what you already have into that sub and you are good to go. Once event code is setup, it will not be changed by later control name changes, it will just never be called.

2. In form2, strOpenArgs is never being changed so you will be in an infinite loop. Replace current code as I gave it to you (sorry, my mistake) between intPos and Select Case with this code:

intPos = InStr(strOpenArgs, &quot;~&quot 'Find first ~
strTemp = Trim$(Left$(strOpenArgs, intPos - 1)) '<== New
strOpenArgs = Trim$(Mid$(strOpenArgs, intPos + 1)) '<== New
Select Case intCounter

The above code is splitting strOpenArgs each time into strTemp and the rest of strOpenArgs. Eventually, it will now become a null string.

3. In form2, UserID and DeptID are numeric, so you need to change their select case code as below:

mintUserID = Val(strTemp) '<== Changes a string to a number
mintDeptID = Val(strTemp) '<== assuming it is numeric

4. In form2, add the following code between the Loop and End If of your OpenArgs check to setup the screen textboxes:

Loop
Me.txtDeptID = mintDeptID '<== New Code
Me.txtU_ID = mstrUID '<== New Code
Me.txtUser_ID = mintUserID '<== New Code
Me.txtDeptName = mstrDeptName '<== New Code
Me.txtFirstName = mstrFirstName '<== New Code
Me.txtLastName = mstrLastName '<== New Code
End If

5. Since the above text boxes are unbound, they will not update any recordsets being created or modified by your form unless you do it yourself with code. If you need them to update records, change them to bound controls and set the above values to the default value which will briing them in for any new records.

Good Luck!

 

[mpnorris]

So far everything looks good. Thank you so much. I feel like we made a big step. I will let you know if I have any other troubles. I can't say we are through all of the problems just yet. Thanks again for the help.

 

[mpnorris]

I got a new one for you. I have everything working where the data comes into the form as expected. I have bound two of the text boxes to database fields. When I hit the &quot;Add Record&quot; Button the two bound fields clear. What I would like it to do is hold the values so that the person that enters the form can add multiple records without having to re-log in. Is this possible with the way I have it set up?

Thanks again for all your help.

 

[SBendBuckeye]

Couple ways to do this. The easiest way is to set the bound control's DefaultValue property to your OpenArgs values in your Form Load event code. Another way is to reset them in Form Current code. You could brute force it and reset them every time or just change it when the value is Null which it will be if you are getting a new record for the first time.

As an aside, run time changes DO NOT persist in Access so any property changes you make as above will disappear as soon as the user exits the system.

If you have a development background, you may want to look at the Access2000 Developers Handbook by Getz, Litwin, etc. It is an outstanding reference although it can be overwhelming at times due to the scope and depth of the material presented.

This has been a lot of fun!

Good Luck!

 

[mpnorris]

I tried the easiest way first and it doesn't seem to work I keep getting #Name? in the field after I hit the &quot;Continue&quot; button.

I wasn't exactly sure which value to set the default , so I tried a couple and got the same result.

I set the txt.DeptID property to = mintDeptID
and the txtUser_ID = mintUserID

I then tried the Val(strTemp) and that didn't work. What am I missing?

 

[mpnorris]

When I begin to enter another transaction, the two text boxes change from #Name? to zeros and you can continue on. Any thoughts?