Link to home
Start Free TrialLog in
Avatar of syedasimmeesaq
syedasimmeesaqFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of syedasimmeesaq

ASKER

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

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
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
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.
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.
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
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

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
try after all the me.refresh

me.refresh
me.combo55.value=me.textbox
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 :-)