Solved

populate a textbox based on the results of a query

Posted on 2004-08-02
24
498 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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
 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

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…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

809 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