Solved

How can I make thhis code update the form?

Posted on 2004-03-29
15
321 Views
Last Modified: 2007-12-19
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 As Control, _
 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.RowSource, dbOpenDynaset)
    ' 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.RowSource, dbOpenTable)
    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
0
Comment
Question by:rcleon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 5

Expert Comment

by:jpolin1
ID: 10705744
Here is a good rule of thumb, do not use someone elses
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
0
 

Author Comment

by:rcleon
ID: 10705872
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
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10706357
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
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:rcleon
ID: 10706524
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
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10706560
Rafael
did you do what I wrote

jaffer
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10706598
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
0
 
LVL 5

Expert Comment

by:jpolin1
ID: 10706632
After you call your 2 functions that you have, do the requery and refresh.
0
 

Author Comment

by:rcleon
ID: 10706681
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
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10706733
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
0
 

Author Comment

by:rcleon
ID: 10706808
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
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10706854
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
0
 
LVL 27

Accepted Solution

by:
jjafferr earned 500 total points
ID: 10707340
Rafael
Your problem is that, your txtSearchReportNumber is a 6 digit field, where as your txtReportNumber is nn-nnnn-nn
so here what I did was to convert your txtSearchReportNumber to txtReportNumber  format

Private Sub txtSearchReportNumber_LostFocus()
    [txtReportNumber] = Left([txtSearchReportNumber], 2) & "-" & Mid([txtSearchReportNumber], 3, 4) & "-" & Right([txtSearchReportNumber], 2)
End Sub

I hope this is what you were looking fpr.

jaffer
0
 

Author Comment

by:rcleon
ID: 10707428
Thank you, Thank You,  Thank You

Rafael
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10707467
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
0
 

Author Comment

by:rcleon
ID: 10710326
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question