Solved

populate a textbox based on the results of a query

Posted on 2004-08-02
24
494 Views
Last Modified: 2010-05-18
Hi.. i have a form which contains a drop down menu with names of customers in it. the form populates based on what is selected from that drop down.  this form is based on 'Customer Master Table' (cmt)

i have a textbox in this form that is not bound to any of the fields of this table.  it comes from another table, 'Customer Collections Table' (cct).

Once a value on the form is selected i want to populate this box with its appropriate value from the collections table based on customerName & accNum.

any ideas?

I have already written a query that does this..  where would I put the query?  my research online showed me i might need to use somethin called ADO recordset?  no clue what that is..

this is my query..
SELECT SUM([Collections, Fee & General Ledger Table].[Payment Amount1] + [Collections, Fee & General Ledger Table].[Payment Amount2] + [Collections, Fee & General Ledger Table].[Payment Amount3]) AS TotalAmount
FROM [Collections, Fee & General Ledger Table]
WHERE [Customer Name] = MyForm!custNameBox.Value AND [Account Number] = MyForm!accNumBox.value AND [Date Received] = MyForm!dateBox.value;


and it works fine.. except dont know how to integreate it in the form..

any ideas..?

help much appreciated..

thanks,

samarth
0
Comment
Question by:samarth13
  • 12
  • 11
24 Comments
 
LVL 2

Expert Comment

by:MGardner
ID: 11698433
Hi Samarth

Right click the text box on your form in design view and past the SQL into the Control Source


Malcolm
0
 
LVL 2

Expert Comment

by:deevallabh
ID: 11698503
Hi samarth13,

On The "OnChange " event of the select box you could do something like this (if you using DAO) :

        Dim strTargetName
        Dim dbConn As Database
        Dim Rst As DAO.Recordset
        Set dbConn = CurrentDb
               
        'Get list of jusristions that set the final rating score
        Dim strsql

        strSQL = "SELECT SUM([Collections, Fee & General Ledger Table].[Payment Amount1] + [Collections, Fee & General Ledger Table].[Payment Amount2] + [Collections, Fee & General Ledger Table].[Payment Amount3]) AS                                                                                                            "TotalAmount FROM [Collections, Fee & General Ledger Table]  WHERE [Customer Name] = " & MyForm!custNameBox.Value & " AND [Account Number] = " & MyForm!accNumBox.value & " AND [Date Received] = " &                          MyForm!dateBox.value

        Set Rst = dbConn.OpenRecordset("SELECT TARGET_NAME FROM POTENTIAL_TARGET_INFO WHERE DST_CITY = '" & Me.DST_CITY & "' AND VUL_TOTAL = " & Me.BASIC_VUL_SUM_SCORE)
       
        strTargetName = ""
        If Not Rst.EOF Then
            Me.txtTotalAmount = Rst![TotalAmount]
        End If
       
        Rst.Close
        dbConn.Close
        Set dbConn = Nothing
        Set Rst = Nothing


Hope this helps
0
 
LVL 2

Expert Comment

by:deevallabh
ID: 11698544
Hi SteveL13,


         Dim dbConn As Database
        Dim Rst As DAO.Recordset
        Set dbConn = CurrentDb
           
        Dim strsql

        strSQL = "SELECT NUM FROM MYTABLE WHERE MYNUM NOT IN (SELECT MYNUM + 1 FROM MYTABLE)  ORDER BY MYNUM "

        Set Rst = dbConn.OpenRecordset(strSQL)
   
        If Not Rst.EOF Then
            MsgBox Rst![MYNUM]
        End If
       
        Rst.Close
        dbConn.Close
        Set dbConn = Nothing
        Set Rst = Nothing

Hope this helps
0
 
LVL 1

Author Comment

by:samarth13
ID: 11703947
deevallabh,

can you pls clarify this part of the code u gave me..

Set Rst = dbConn.OpenRecordset("SELECT TARGET_NAME FROM POTENTIAL_TARGET_INFO WHERE DST_CITY = '" & Me.DST_CITY & "' AND VUL_TOTAL = " & Me.BASIC_VUL_SUM_SCORE)
??

this is wat i tried for it as of now.. but it doesn't work..

        Dim strTargetName
        Dim dbConn As Database
        Dim Rst As DAO.Recordset
        Set dbConn = CurrentDb
         
        'Get list of jusristions that set the final rating score
        Dim strsql

        strsql = "SELECT SUM([Collections, Fee & General Ledger Table].[Payment Amount1] + [Collections, Fee & General Ledger Table].[Payment Amount2] + [Collections, Fee & General Ledger Table].[Payment Amount3]) AS TotalAmount FROM [Collections, Fee & General Ledger Table]  WHERE [Customer Name] = '" & Me![Borrower Name].Value & "' AND [Account/ Note Number] = '" & Me![Account/ Note Number].Value & "'"

        Set Rst = dbConn.OpenRecordset("SELECT TotalAmount FROM [Collections, Fee & General Ledger Table] WHERE [Customer Name] = '" & Me.[Borrower Name] & "' AND [Account/ Note Number] = '" & Me.[Account/ Note Number] & "'")
     
       
        strTargetName = ""
        If Not Rst.EOF Then
            Me.Text220 = Rst![totalAmount]
            'Me.txtTotalAmount = Rst![totalAmount]
        End If

       
        Rst.Close
        dbConn.Close
        Set dbConn = Nothing
        Set Rst = Nothing

End Sub

thanks,

=samarth=
0
 
LVL 1

Author Comment

by:samarth13
ID: 11703983
oh sorry,

 'Me.txtTotalAmount = Rst![totalAmount]


wasn't commented out in my code..

i had  Me.txtTotalAmount = Rst![TotalAmount] (because my column name is capital in the sql i wrote..

any ideas?
0
 
LVL 1

Author Comment

by:samarth13
ID: 11704052
also, another piece of information that i had overlooked in my first post.. i am NOT using a combo box to select the 'customer name'.. i actually have just put up a 'find' button above a text box that finds the record.. (it was set up that way originally and is more comfortable for the users to use)..

still an 'onChange' event should take care of it, rite?  well, just wanted to bring it to ur attention..

thanks,

samarth
0
 
LVL 2

Expert Comment

by:deevallabh
ID: 11704066
Hi

You can ignore this part, pasted that part of code in by mistake.

Set Rst = dbConn.OpenRecordset("SELECT TARGET_NAME FROM POTENTIAL_TARGET_INFO WHERE DST_CITY = '" & Me.DST_CITY & "' AND VUL_TOTAL = " & Me.BASIC_VUL_SUM_SCORE)
??.


Does the text box get a value or do you get an error when executing the code.

Here's the new code:

       Dim strTargetName
        Dim dbConn As Database
        Dim Rst As DAO.Recordset
        Set dbConn = CurrentDb
         
        'Get list of jusristions that set the final rating score
        Dim strsql

        Set Rst = dbConn.OpenRecordset("SELECT TotalAmount FROM [Collections, Fee & General Ledger Table] WHERE [Customer Name] = '" & Me.[Borrower Name] & "' AND [Account/ Note Number] = '" & Me.[Account/ Note Number] & "'")
     
       
       If Not Rst.EOF Then
            Me.Text220 = Rst![totalAmount]
            'Me.txtTotalAmount = Rst![totalAmount]
        Else
            'Can find out if the record does exist.
            MsgBox "No records were found"
        End If

       
        Rst.Close
        dbConn.Close
        Set dbConn = Nothing
        Set Rst = Nothing

End Sub







Also, try excuting the query in the query tool to see if you get any results.


-Dee
0
 
LVL 1

Author Comment

by:samarth13
ID: 11704267
hi,

i tried some debugging, and this is just flustering.. its put me a few steps behind.. i figured out that the code that u had given me wasn't even executing..

i have a textbox called 'borrowerName'.. for which i use a find button to find the record.. for that textBox in the OnChange and AfterUpdate event, i have this code..

Private Sub borrowerName_AfterUpdate()
Debug.Print "updated!"
End Sub

Private Sub borrowerName_Change()
Debug.Print "changed"
End Sub

everytime i find a new name, and the new record shows up.. this code never gets executed.. why wouldn't it..??  sorry to take a few steps bak..
0
 
LVL 2

Expert Comment

by:deevallabh
ID: 11704377
Hi,

Using the find event, or modifying the values in code will not trigger events any of the controls. You might want to use the forms "OnCurrent" event. I think this should work.

-Dee
0
 
LVL 1

Author Comment

by:samarth13
ID: 11704447
yea.. that works.. thx.. i just thot that the find wud trigger the "onChange" event, since we are changing the value in the box..

anyway.. so now the event triggers. but i get an error..

"Error 3061.  Too few parameters.  Expected 1."

and its on this line..
Set Rst = dbConn.OpenRecordset("SELECT TotalAmount FROM [Collections, Fee & General Ledger Table] WHERE [Customer Name] = '" & Me.[Borrower Name] & "' AND [Account/ Note Number] = '" & Me.[Account/ Note Number] & "'")
     

any ideas?

thanks,

samarth
0
 
LVL 2

Expert Comment

by:deevallabh
ID: 11704623
Hi,

Typically, the "too few parameters" means that you mispelled column or table names in the query. If this is not the case, prin the sql string to the debug window and paste it in the query tool, be alot easier to debug.

-dee
0
 
LVL 1

Author Comment

by:samarth13
ID: 11704788
ok.. i tried the query.. it runs fine.. the onle other discrepancy in terms of column name that i cud see is in my query..

strsql = "SELECT SUM([Collections, Fee & General Ledger Table].[Payment Amount1] + [Collections, Fee & General Ledger Table].[Payment Amount2] + [Collections, Fee & General Ledger Table].[Payment Amount3]) AS TotalAmount FROM [Collections, Fee & General Ledger Table]  WHERE [Customer Name] = '" & Me![Borrower Name].Value & "' AND [Account/ Note Number] = '" & Me![Account/ Note Number].Value & "'"
       

i have added up three columns and taken the sum of it, adn called the new column TotalAmount..

could that be the source of the error in
Set Rst = dbConn.OpenRecordset("SELECT TotalAmount FROM [Collections, Fee & General Ledger Table] WHERE [Customer Name] = '" & Me.[Borrower Name] & "' AND [Account/ Note Number] = '" & Me.[Account/ Note Number] & "'")

and if so.. what should i Do about it?

thanks,
       
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Expert Comment

by:deevallabh
ID: 11704995
Hi

You should use this query

strsql = "SELECT SUM([Collections, Fee & General Ledger Table].[Payment Amount1] + [Collections, Fee & General Ledger Table].[Payment Amount2] + [Collections, Fee & General Ledger Table].[Payment Amount3]) AS TotalAmount FROM [Collections, Fee & General Ledger Table]  WHERE [Customer Name] = '" & Me![Borrower Name].Value & "' AND [Account/ Note Number] = '" & Me![Account/ Note Number].Value & "'"

Set Rst = dbConn.OpenRecordset(strSQL)
vs

Set Rst = dbConn.OpenRecordset("SELECT TotalAmount FROM [Collections, Fee & General Ledger Table] WHERE [Customer Name] = '" & Me.[Borrower Name] & "' AND [Account/ Note Number] = '" & Me.[Account/ Note Number] & "'")

The reason being, is TotalAmount does not exist as a column in "Collections, Fee & General Ledger Table" table and thats probably why u getting an error

0
 
LVL 1

Author Comment

by:samarth13
ID: 11705108
yes, while i was waiting for your reply, i realized that.. and changed it to what you have above.. but then teh problem would lie within the line..

Me.text220 = Rst.[TotalAmount].. i m getting a complie error..  "Method or member data not found"... on this line..
Me.Text220 = Rst.[totalAmount]

any ideas?

i think this shud be it..  i shud be able to pull it off after this one last glitch..

thanks,

0
 
LVL 2

Accepted Solution

by:
deevallabh earned 175 total points
ID: 11705184
Hi

Should be :  Rst![totalAmount], it not try Rst(0) where "0" indicates the first column in the query.

-Dee


0
 
LVL 1

Author Comment

by:samarth13
ID: 11705251
     If Not Rst.EOF Then
            Me.Text220.SetFocus
            Me.Text220.Text = Rst![totalAmount]

Error: "The property is Read-Only and cant be set."

any ideas?

0
 
LVL 2

Expert Comment

by:deevallabh
ID: 11705277


Try

Me.Text220 = Rst![totalAmount]
0
 
LVL 1

Author Comment

by:samarth13
ID: 11705321
Run-time error '-21473522567 (800200009'

You can't assign a value to this object.
0
 
LVL 2

Expert Comment

by:deevallabh
ID: 11705523


Try Me.Txt220.value = Rst![totalAmount]

0
 
LVL 1

Author Comment

by:samarth13
ID: 11705604
run time error 2448
you cant assing a value to this object.

sorry for the trouble..

but any ideas why?  mabe because we are trying to set a column value to a text field..??  is there anyway to get a particular cell in that column?  how is the data stored in the recordset.. is it a 2D array?

thanks,
0
 
LVL 2

Expert Comment

by:deevallabh
ID: 11705638
Instead of Assigning it a text box, try msgbox the value. Other ways of getting the value is to use the Rst.Fields("totalAmount") or Rst(0) or Rst.Fields(0)
0
 
LVL 1

Author Comment

by:samarth13
ID: 11705876
i do get the correctly value fromt he recordset.. i printed that.. and its all good..

the only error i have is.. error 2135:

"this property is read-only' and cant be set..

any way to change the read-only boolean?

thanks,
0
 
LVL 2

Expert Comment

by:deevallabh
ID: 11705978
Is "txt220" control a text box. Did you set any specific properties on the text box control? Try dragging another text box to the form and assigning a value to it.
0
 
LVL 1

Author Comment

by:samarth13
ID: 11706080
jeez dude.. thx a lot.. really appreciate it.. i'll give u points rite after i get bak.. have to get runnin onw.. but, it works now! thx.. really really appreciated!

cheers,
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now