[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

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
0
syedasimmeesaq
Asked:
syedasimmeesaq
  • 6
  • 5
1 Solution
 
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
 
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now