Field entry from function that looks up data from query or SQL 
Author Message
 Field entry from function that looks up data from query or SQL

I have a table that has a ContactID field (counter) , AssociateID field
(number) this is used to associate family members to the
Paternal member (father), additional fields are Names, address, city,
etc. Also a Family Names field (text).  What I would like
to do is upon entering the Family names field have a function run that
will query the database for the matching AssociateID's
(children) that match the ContactID (parent), and add each first name to
the Family Names field seperated by a comma. (John ,
Jane , Joan).

I have a query that gives me the proper info that I am looking to
retreive. It (qryFamilyNames) returns only the FirstName from
the Members Table where AssociateID = ContactID.  At times there are 7
names in the returned datasheet.

I have tried without success to write this function, could someone point
me in the proper direction.  Below is what I have been
trying to do but it won't work and I get a repetative error, (Too few
parameters. Expected 1, Runtime Error 3061)  The
parameter part I am confussed on also.

Public Function getFamilyNames()
Dim db As Database, ds As Dynaset, tempNames As String, SQL
Set db = OpenDatabase("HCUMC_APP.MDB")

'  create a dynaset from an SQL to get names from

SQL = "SELECT [Members].[FirstName] FROM [Members] WHERE
((([Members]![AssociatedID])=[Forms]![Members]![ContactID]));"

Set ds = db.CreateDynaset(SQL)         ' This is where the ERROR comes
in....

' From the Dynaset create a lineal list of names from the "FirstName"
column

      Do Until ds.EOF
        tempNames = tempNames & " , " & ds![FirstName] 'ie, " John ,
Jane , Jack "
        ds.MoveNext
      Loop
        getFamilyNames = tempNames
    ds.Close

End Function



Thu, 22 Mar 2001 03:00:00 GMT  
 Field entry from function that looks up data from query or SQL
Try changing SQL string to:

SQL = "SELECT [Members].[FirstName] FROM [Members] WHERE
((([Members]![AssociatedID])=" & [Forms]![Members]![ContactID] & "));"

Quote:

>I have a table that has a ContactID field (counter) , AssociateID field
>(number) this is used to associate family members to the
>Paternal member (father), additional fields are Names, address, city,
>etc. Also a Family Names field (text).  What I would like
>to do is upon entering the Family names field have a function run that
>will query the database for the matching AssociateID's
>(children) that match the ContactID (parent), and add each first name to
>the Family Names field seperated by a comma. (John ,
>Jane , Joan).

>I have a query that gives me the proper info that I am looking to
>retreive. It (qryFamilyNames) returns only the FirstName from
>the Members Table where AssociateID = ContactID.  At times there are 7
>names in the returned datasheet.

>I have tried without success to write this function, could someone point
>me in the proper direction.  Below is what I have been
>trying to do but it won't work and I get a repetative error, (Too few
>parameters. Expected 1, Runtime Error 3061)  The
>parameter part I am confussed on also.

>Public Function getFamilyNames()
>Dim db As Database, ds As Dynaset, tempNames As String, SQL
>Set db = OpenDatabase("HCUMC_APP.MDB")

>'  create a dynaset from an SQL to get names from

>SQL = "SELECT [Members].[FirstName] FROM [Members] WHERE
>((([Members]![AssociatedID])=[Forms]![Members]![ContactID]));"

>Set ds = db.CreateDynaset(SQL)         ' This is where the ERROR comes
>in....

>' From the Dynaset create a lineal list of names from the "FirstName"
>column

>      Do Until ds.EOF
>        tempNames = tempNames & " , " & ds![FirstName] 'ie, " John ,
>Jane , Jack "
>        ds.MoveNext
>      Loop
>        getFamilyNames = tempNames
>    ds.Close

>End Function



Sat, 24 Mar 2001 03:00:00 GMT  
 Field entry from function that looks up data from query or SQL
Thank you Alyda,  That did help.  Problem has been solved with the help of
you and another persons knowledge.
Quote:

>Try changing SQL string to:

>SQL = "SELECT [Members].[FirstName] FROM [Members] WHERE
>((([Members]![AssociatedID])=" & [Forms]![Members]![ContactID] & "));"



Tue, 03 Apr 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. LOOKING FOR ENTRY LEVEL PROGRAM ANALYST POSITION WITH VB,SQL, MS-Access EXPRIENCE

2. need to put multipe data entries in new field

3. How make a field require data entry if any checkbox is checked

4. controlling data entry in text form fields

5. How to temporarily change BackgroundColor of Data Entry Field with Focus

6. VB4 How to temporarily change BackgroundColor of Data Entry field in focus

7. Freeware: UPS real-time shipping charge query tool

8. Freeware: UPS real-time shipping charge query tool

9. Looking for one word in a SQL Query

10. Best site for freelancer work (PHP, Web site, java, oracle sql, php, data entry)

11. Pass a query's field Value to Function

12. How to refer the fields from a query in a Function

 

 
Powered by phpBB® Forum Software