Combo Refreshes with form Me.Refresh

I have a very complicated problem

In my form, I have command button 25,26,27 that refresh the forms to get the values in the forms text field. I have a combo box there too. I choose the value from combo55 and then enter what I need to enter and then click the buttons 36, 26 and 25 accordingly. All three of the buttons run three different queries and represent the user with the values in same form.
Now problem is that when I click any of those buttons, it refreshes my form and I loose the focus of the selected item in the combo box and it gets selected at its default value for the first item in the combo box list. What I want to do is some how control that combobox so whatever happens when its value is selected it couldn't be changed on form refresh, unless the form is closed and reopened.

here are some codes for experts

Combo Box:

Row Source:
SELECT tbl.EmployeeID, tbl.FirstName, tbl.LastName, tbl.Number, tbl.Returned, tbl.ReturnDate, tbl.DateA, tbl.IDNUMBER FROM tbl WHERE (((tbl.Returned)=-1) AND ((tbl.Paid)=0)) ORDER BY tbl.FirstName, tbl.LastName;

Column Count: 8
Column Heads: Yes
Column Width: 0";1",1",1";1"
Bound Column; 1
List Row: 8
List Width: 3"


Form:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

    Dim stDocName As String

    stDocName = "less80"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    Me.Refresh
Exit_Command25_Click:
    Exit Sub

Err_Command25_Click:
    MsgBox Err.Description
    Resume Exit_Command25_Click
   
End Sub
Private Sub Command26_Click()
On Error GoTo Err_Command26_Click

    Dim stDocName As String

    stDocName = "over80"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    Me.Refresh

Exit_Command26_Click:
    Exit Sub

Err_Command26_Click:
    MsgBox Err.Description
    Resume Exit_Command26_Click
   
End Sub
Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

    Dim stDocName As String

    stDocName = "Macro4"
    DoCmd.RunMacro stDocName
    Me.Refresh
Exit_Command27_Click:
    Exit Sub

Err_Command27_Click:
    MsgBox Err.Description
    Resume Exit_Command27_Click
   
End Sub





Private Sub Form_BeforeUpdate(Cancel As Integer)
Me![mydate].Value = Format(Now(), "MM/DD/YY")
End Sub

Private Sub Form_Close()
Me.Refresh
End Sub

Private Sub Form_Current()
Me![mydate].Value = Format(Now(), "MM/DD/YY")
End Sub

Private Sub Form_Load()
DoCmd.SetWarnings False
End Sub

Private Sub Command36_Click()
On Error GoTo Err_Command36_Click

    Dim stDocName As String

    stDocName = "percent"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    Me.Refresh
   
   
Exit_Command36_Click:
    Exit Sub

Err_Command36_Click:
    MsgBox Err.Description
    Resume Exit_Command36_Click
   
End Sub

Private Sub Command38_Click()
On Error GoTo Err_Command38_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.PrintOut acSelection

Exit_Command38_Click:
    Exit Sub

Err_Command38_Click:
    MsgBox Err.Description
    Resume Exit_Command38_Click
   
End Sub


Private Sub Command60_Click()
On Error GoTo Err_Command60_Click


    DoCmd.Close

Exit_Command60_Click:
    Exit Sub

Err_Command60_Click:
    MsgBox Err.Description
    Resume Exit_Command60_Click
   
End Sub
Private Sub Command61_Click()
On Error GoTo Err_Command61_Click

    Dim stDocName As String

    stDocName = "Employees"
    DoCmd.OpenReport stDocName, acPreview

Exit_Command61_Click:
    Exit Sub

Err_Command61_Click:
    MsgBox Err.Description
    Resume Exit_Command61_Click
   
End Sub
Private Sub closeopenform_Click()
On Error GoTo Err_closeopenform_Click

    Dim stDocName As String

    stDocName = "closeopenmaxpayout1"
    DoCmd.RunMacro stDocName

Exit_closeopenform_Click:
    Exit Sub

Err_closeopenform_Click:
    MsgBox Err.Description
    Resume Exit_closeopenform_Click
   
End Sub


Private Sub PAYthem_Click()
CurrentDb.Execute _
    "update tbl set Paid = -1 Where IDNumber=" & Me.Listemploypay.Column(7)
   
On Error GoTo Err_closeopenform_Click

    Dim stDocName As String

    stDocName = "closeopenmaxpayout1"
    DoCmd.RunMacro stDocName

Exit_closeopenform_Click:
    Exit Sub

Err_closeopenform_Click:
    MsgBox Err.Description
    Resume Exit_closeopenform_Click
End Sub


Hope someone has answer for this. Thanks
LVL 9
syedasimmeesaqAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
here is one way to get the value selected from the combo box to be selected again after refresh

place a textbox in your form, set this to be small and not visible
in the after update of the combo store the value in the textbox

private sub combo_afterupdate()

me.hiddentextbox=me.combo

end sub


then just refer to the textbox to set the value of the combo


me.combo.value=me.hiddentextbox

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
syedasimmeesaqAuthor Commented:
Problem with that is that I am using the column values from the combo box and texbox doesn't have combo values.

0
syedasimmeesaqAuthor Commented:
I can use

txtbox.value = me.combobox.column(which I want to use)

but it will give me only one column per box....what if I wanna use 6

I would need six boxes then.

Anyway around??

Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
you don't have too. just try the textbox idea.

just set the  textbox  to the default combo bound column like i posted it.

me.hiddentextbox=me.combo
0
syedasimmeesaqAuthor Commented:
Ok it doesn't work on this part

CurrentDb.Execute _
    "update tbl set Paid = -1 Where IDNumber=" & Me.textbox

as I was getting the value like this

CurrentDb.Execute _
    "update tbl set Paid = -1 Where IDNumber=" & Me.Listemploypay.Column(7)


so the column(7) in combobox contains that value of IDNUMBER and with above I can't do that.
0
Rey Obrero (Capricorn1)Commented:
you are missing the whole point of the textbox.
don't use the textbox value for your update

use the textbox value to go back to the selected value in the combo box,
by

me.combo.value=me.textbox

then use the combo for your update.
0
syedasimmeesaqAuthor Commented:
I couldn't figure out where to put that at?

I tried putting it like this

In combo55_afterupdate()

Me.textbox = Me.Combo55

And later where I wanna use it. I did this


Me.combo55.value = me.textbox
CurrentDb.Execute _
    "update tbl set Paid = -1 Where IDNumber=" & Me.combo55.Column(7)

but no results

Thanks
0
Rey Obrero (Capricorn1)Commented:
to test this,

create a new form with 1 textbox, 1 combo, two command button

set the row source of your combo  the same  as the one you used for your combo55

private sub combo_afterupdate()

me.textbox=Me.combo

end sub

                in the first command button place this code

private sub command1_click()

me.combo=""

end sub


                in the second command button

private sub command2_click()

me.combo.value=me.textbox

msgbox me.combo.column(7)

end sub

0
syedasimmeesaqAuthor Commented:
Capricorn1 I know what you mean by that but my form is getting Refresh every time. Once it get refreshed the combo loses its value. Please see the command button codes (click();36,25,26) in my question. Your suggestion will work fine if the nested nesty Me.refreshES are not there. But since they are there I have to work with them. If I take them out, it will kill other purpose.
BTW I tried your all the methods, but am still back to where we started.

Thanks
0
Rey Obrero (Capricorn1)Commented:
try after all the me.refresh

me.refresh
me.combo55.value=me.textbox
0
syedasimmeesaqAuthor Commented:
Well it didn't work for some reason. However, I append he value from combo to text and for each column and then used that column. Couldn't use it all at once so had to make 6 text boxes.
Thank you for looking out
See u soon again :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.