syedasimmeesaq
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
just set the textbox to the default combo bound column like i posted it.
me.hiddentextbox=me.combo
ASKER
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.
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.
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.
ASKER
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
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
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
ASKER
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
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.textbo x
me.refresh
me.combo55.value=me.textbo
ASKER
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 :-)
Thank you for looking out
See u soon again :-)
ASKER