How can I make thhis code update the form?
Hi,
I found this module and I implemented i my project and it's doing just what I needed with one exception. Once the user click on the company name I need the form to refresh with all of the company information.
The text box and list box are located in the form header the form name is COMPANYINFO.
Please help
Rafael
Option Compare Database
Option Explicit
Const acbcErrNoError = 0
Const acbcQuote = """"
Public Function acbDoSearchDynaset(ctlText
ctlList As Control, strBoundField As String) As Variant
' Search through a bound listbox, given text to find from a text box.
' Move the list box to the appropriate row.
' The listbox can have either a table or a dynaset
' (a query or an SQL statement) as its row source.
' In:
' ctlText: a reference to the text box you're typing into
' ctlList: a reference to the list box you're looking up in
' strBoundField: the name of the field in the underlying
' table in which you're looking for values.
' Out:
' Return value: either 0 (no error) or an error variant
' containing the error number.
Dim rst As DAO.Recordset
Dim varRetval As Variant
Dim db As DAO.Database
On Error GoTo HandleErr
Set db = CurrentDb()
Set rst = db.OpenRecordset(ctlList.R
' Use the .Text property, because you've not yet left the
' control. Its value (or its .Value property) aren't
' set until you leave the control.
rst.FindFirst "[" & strBoundField & "] >= " & acbcQuote & ctlText.Text & acbcQuote
If Not rst.NoMatch Then
ctlList = rst(strBoundField)
End If
varRetval = acbcErrNoError
ExitHere:
acbDoSearchDynaset = varRetval
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Function
HandleErr:
varRetval = CVErr(Err)
Resume ExitHere
End Function
Public Function acbDoSearchTable(ctlText As Control, ctlList As Control, _
strBoundField As String, strIndex As String) As Variant
' Search through a bound listbox, given text to find from a text box.
' Move the list box to the appropriate row.
' The listbox can have only a table as its
' row source.
' In:
' ctlText: a reference to the text box you're typing into
' ctlList: a reference to the list box you're looking up in
' strBoundField: the name of the field in the underlying
' table in which you're looking for values.
' strIndex: the name of the index to look through.
' Out:
' Return value: either 0 (no error) or an error variant
' containing the error number.
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varRetval As Integer
On Error GoTo HandleErr
Set db = CurrentDb()
Set rst = db.OpenRecordset(ctlList.R
rst.Index = strIndex
' Use the .Text property, because you've not yet let the
' control. Its value (or its .Value property) aren't
' set until you leave the control.
rst.Seek ">=", ctlText.Text
If Not rst.NoMatch Then
ctlList = rst(strBoundField)
End If
varRetval = acbcErrNoError
ExitHere:
On Error Resume Next
rst.Close
On Error GoTo 0
acbDoSearchTable = varRetval
Exit Function
HandleErr:
varRetval = CVErr(Err)
Resume ExitHere
End Function
Sub acbUpdateSearch(ctlText As Control, ctlList As Control)
ctlText = ctlList
End Sub
I found this module and I implemented i my project and it's doing just what I needed with one exception. Once the user click on the company name I need the form to refresh with all of the company information.
The text box and list box are located in the form header the form name is COMPANYINFO.
Please help
Rafael
Option Compare Database
Option Explicit
Const acbcErrNoError = 0
Const acbcQuote = """"
Public Function acbDoSearchDynaset(ctlText
ctlList As Control, strBoundField As String) As Variant
' Search through a bound listbox, given text to find from a text box.
' Move the list box to the appropriate row.
' The listbox can have either a table or a dynaset
' (a query or an SQL statement) as its row source.
' In:
' ctlText: a reference to the text box you're typing into
' ctlList: a reference to the list box you're looking up in
' strBoundField: the name of the field in the underlying
' table in which you're looking for values.
' Out:
' Return value: either 0 (no error) or an error variant
' containing the error number.
Dim rst As DAO.Recordset
Dim varRetval As Variant
Dim db As DAO.Database
On Error GoTo HandleErr
Set db = CurrentDb()
Set rst = db.OpenRecordset(ctlList.R
' Use the .Text property, because you've not yet left the
' control. Its value (or its .Value property) aren't
' set until you leave the control.
rst.FindFirst "[" & strBoundField & "] >= " & acbcQuote & ctlText.Text & acbcQuote
If Not rst.NoMatch Then
ctlList = rst(strBoundField)
End If
varRetval = acbcErrNoError
ExitHere:
acbDoSearchDynaset = varRetval
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Function
HandleErr:
varRetval = CVErr(Err)
Resume ExitHere
End Function
Public Function acbDoSearchTable(ctlText As Control, ctlList As Control, _
strBoundField As String, strIndex As String) As Variant
' Search through a bound listbox, given text to find from a text box.
' Move the list box to the appropriate row.
' The listbox can have only a table as its
' row source.
' In:
' ctlText: a reference to the text box you're typing into
' ctlList: a reference to the list box you're looking up in
' strBoundField: the name of the field in the underlying
' table in which you're looking for values.
' strIndex: the name of the index to look through.
' Out:
' Return value: either 0 (no error) or an error variant
' containing the error number.
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varRetval As Integer
On Error GoTo HandleErr
Set db = CurrentDb()
Set rst = db.OpenRecordset(ctlList.R
rst.Index = strIndex
' Use the .Text property, because you've not yet let the
' control. Its value (or its .Value property) aren't
' set until you leave the control.
rst.Seek ">=", ctlText.Text
If Not rst.NoMatch Then
ctlList = rst(strBoundField)
End If
varRetval = acbcErrNoError
ExitHere:
On Error Resume Next
rst.Close
On Error GoTo 0
acbDoSearchTable = varRetval
Exit Function
HandleErr:
varRetval = CVErr(Err)
Resume ExitHere
End Function
Sub acbUpdateSearch(ctlText As Control, ctlList As Control)
ctlText = ctlList
End Sub
ASKER
OK got the message and You are right but I'm desperated for an answer.
So where in the form do I place requery and refresh comand? Do I place that inside the module?
Thanks
Rafael
So where in the form do I place requery and refresh comand? Do I place that inside the module?
Thanks
Rafael
Hi rcleon,
Go to the properties of the field where the company name is,
go to On Click, press on the little triangle on the right, and select [Event Procedure], then press on the button with 3 dots next to the little triangle,
this will take you to VB editor,
there type
Refresh
Hope this helps
jaffer
Go to the properties of the field where the company name is,
go to On Click, press on the little triangle on the right, and select [Event Procedure], then press on the button with 3 dots next to the little triangle,
this will take you to VB editor,
there type
Refresh
Hope this helps
jaffer
ASKER
Here is the chalance went I click the right report number on the list box it updates the text box with the complete information at that time it should refresh the form. But it is not working.
Rafael
Rafael
Rafael
did you do what I wrote
jaffer
did you do what I wrote
jaffer
Is your Form Source Record is a Table or a Query(or SQL)?
if it was a Table, then do like I told you earlier,
if it was a Query (or SQL), the write this instead of what I told you:
YourFormNam.Requery
jaffer
if it was a Table, then do like I told you earlier,
if it was a Query (or SQL), the write this instead of what I told you:
YourFormNam.Requery
jaffer
After you call your 2 functions that you have, do the requery and refresh.
ASKER
Jaffer
Here it is
Private Sub txtCompany Name_Click()
Me.Requery
Me.Refresh
End Sub
Yes the Form Record Source is a table.
Why not place this code on the text box where the info get updated?
There is a text box that is tide to a list box went I start typing on the text box the list box scroll then I click on the list box for the record I need this updates the text box I was originaly typing. Once this text box is update with the complete information then it should refresh the form.
Rafael
Here it is
Private Sub txtCompany Name_Click()
Me.Requery
Me.Refresh
End Sub
Yes the Form Record Source is a table.
Why not place this code on the text box where the info get updated?
There is a text box that is tide to a list box went I start typing on the text box the list box scroll then I click on the list box for the record I need this updates the text box I was originaly typing. Once this text box is update with the complete information then it should refresh the form.
Rafael
Your are right, you can do that too.
1-
Lets make sure everything is OK, before you click on the company name, go to the menu>records>refresh and see if it refreshes the values.
2-
you don't need bothe the Refresh and the Requery, you choose either.
jaffer
1-
Lets make sure everything is OK, before you click on the company name, go to the menu>records>refresh and see if it refreshes the values.
2-
you don't need bothe the Refresh and the Requery, you choose either.
jaffer
ASKER
Jaffer,
No nothing happens. I click on the company name I needed the went record refresh and the form did not change it's still shows the last company Name on the table. The form open displaying the last record.
Thanks
Rafael
No nothing happens. I click on the company name I needed the went record refresh and the form did not change it's still shows the last company Name on the table. The form open displaying the last record.
Thanks
Rafael
Rafael
please zip your mdb file and email it to me, let me have a look at it.
to get my email, click on my nme which will show you my profile.
jaffer
please zip your mdb file and email it to me, let me have a look at it.
to get my email, click on my nme which will show you my profile.
jaffer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, Thank You, Thank You
Rafael
Rafael
You are welcome
I am glad I was of help,
thanks for the points and the grade,
BTW, The logic of the Form was OK,
did you understand what was your problem?
jaffer
I am glad I was of help,
thanks for the points and the grade,
BTW, The logic of the Form was OK,
did you understand what was your problem?
jaffer
ASKER
Yes I look back at all the code and was able to understand the problem. Your where rught I should not use code from book or other sources if I do not undertand how it works.
Again Thanks
Rafael
Again Thanks
Rafael
code if you do not know how it works.
how about trying doing this on the form you want to update.
After calling the function,
me.requery
me.refresh