We help IT Professionals succeed at work.

Combo Refreshes with form Me.Refresh

syedasimmeesaq
on
Medium Priority
561 Views
Last Modified: 2008-02-26
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Problem with that is that I am using the column values from the combo box and texbox doesn't have combo values.

Author

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
CERTIFIED EXPERT
Top Expert 2016

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

Author

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.
CERTIFIED EXPERT
Top Expert 2016

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.

Author

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
CERTIFIED EXPERT
Top Expert 2016

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

Author

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
CERTIFIED EXPERT
Top Expert 2016

Commented:
try after all the me.refresh

me.refresh
me.combo55.value=me.textbox

Author

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 :-)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.