Combining Same Field Values in a Query... 3

[SherryLynn]

I am hoping someone here has an easy answer for me. I have a database that tracks families and their children. One of my main tables (Family Table) records the Family's Last Name, File No., etc. The other table (Child Table) records the name of each child in the family (there can be 1 or 10 children), date of birth, etc. I want to create a query to make File labels on each family. The label should include the Last Name, File No. and all the children's names separated by commas. Here's what I want it to look like:

SMITH xx JONES S003394
John, Mary, Ken, Shelley

I have created a make table query on these fields. The table it creates includes a record for each child:

SMITH xx JONES S003394 John
SMITH xx JONES S003394 Mary
SMITH xx JONES S003394 Ken
SMITH xx JONES S003394 Shelley

I would like it to create a table that makes one record for the family:

SMITH xx JONES S003394 John, Mary, Ken, Shelley

I appreciate any and all help. I haven't been able to even get close to a solution.

Thanks,
Sherry

 

[PaulBricker]

I don't know. 97 recognizes rst.MoveFirst so it's a bit of a mystery. I do know that 97 normally uses the the DAO 2.5/3.5 Object Library and not 3.6 but that shouldn't be the issue. Once more, copy and paste the code to the forum and I'll look again. It's got to be something simple. Any chance you can email it to me.
I use 2000 but can convert without issues.

Paul

 

[Transcend]

Runtime error 91 is Object variable not set

are you sure your query is actually returning anything to your recordset? Put a stop in where you build you SQL statement to see what the value of strsql is. Then copy and paste that query into access to see if it returns anything. You can't move to the first record if there is nothing in the recordset.

Transcend

 

[PaulBricker]

Transcend, I thought of that but usually you get a different error message (No current Record) if you try to move to the first record and there aren't any records returned by the recordset. But I would certainly welcome any suggestions at this point. I feel like it's got to be some small problem we've overlooked.

Paul

 

[SherryLynn]

Hi Paul,

Here is the code:


Option Compare Database
Option Explicit

Function getFamily(myID As String) As String
Dim strSQL As String
Dim strHolder As String, strFam As String
Dim rst As DAO.Recordset
strSQL = "Select * From [tblLabels-Export] Where [tblLabels-Export].[FamilyID]= " '& myID & "'"Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
Do Until rst.EOF
strFam = rst![Family] & " " & myID
strHolder = strHolder & rst![Name] & " ,"
rst.MoveNext
Loop
getFamily = strFam & " " & Mid(strHolder, 1, Len(strHolder) - 2)

End Function

 

[Transcend]

strSQL = "Select * From [tblLabels-Export] Where [tblLabels-Export].[FamilyID]= " '& myID & "'"Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)


This is wrong you copied it incorrectly. That is supposed to be two different pieces of code.

Change it to:

strSQL = "Select * From [tblLabels-Export] Where [tblLabels-Export].[FamilyID]= " '& myID

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)



Transcend

 

[Transcend]

Actually make it

strSQL = "Select * From [tblLabels-Export] Where [tblLabels-Export].[FamilyID]= " & myID

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

there was an extra ' in there

Transcend

 

[PaulBricker]

Sherry, each time you post the code, I see the "Set rst=" following the strSQL line and then the next line is
CurrentDB.OpenRecordset(strSQL,dbOpenDynaset). I think that's our problem. In the code module make sure that the line that starts with
strSQL = "Select....
looks like this
strSQL = "Select * From [tblLabels-Export] Where [tblLabels-Export].[FamilyID]= '" & myID & "'"

and is all on one line. Your syntax in the code you posted was not correct either so copy and paste this line directly into the module. If it shows on two line, backspace the second line till it's all on the same line. Then the next line is
Set rst = CurrentDb.OpenRecordset(strSQL,dbOpenDynaset)

That has to be all on it's own line also. I think what we had was a run on between the two lines of code that was messing stuff up.

Paul

 

[PaulBricker]

Transcend, the strSQL you posted would work if myID was numeric, but it's text and has to be surrounded by single/double quotes. When she reversed it, it commented out the last part of the string which is why it's not working, plus the Set statement was separated from the rest of the line so we never set rst to anything. That's where the "Object or with block variable not set" error comes from.

Paul

 

[Transcend]

Yeah I just noticed that when I read your post
Then I read up and realised it needed a string.
Sherry post paul's code as mine is not quite right

Transcend

 

[SherryLynn]

Thanks to both of you. I am trying this now, will let you know.

Sherry

 

[SherryLynn]

Hello,

Ok, I copied the code into the module as you had said. I tried to run the query again and now get this message:

Run-time error '3464':
Data type mismatch in criteria expression.

Help!

Thanks, Sherry

 

[Transcend]

Ok just to be sure can you paste here what you have in your module?

Transcend

 

[cuok]

PaulBricker !



AS ALWAYS U R GREAT !

CUOK

 

[PaulBricker]

Sherry, usually this means that our quote and apostrophe's don't match what the Datatype of our criteria is. Make sure that the end of your SQL string looks like this.

' " & myID & " ' "

Now I have put spaces inbetween the apostrophe's and quote marks so it is easier to read but there should be not spaces between the first apostrophe and quote and between the trailing quote/apostrophe/quote marks.

Paul

 

[SherryLynn]

Hi Paul,

I have checked on that and it seems to be right in the module. I have copied and posted the code here again. Does it look right? It's running over to two lines here, but is on one line in the module:

Function getFamily(myID As String) As String
Dim strSQL As String
Dim strHolder As String, strFam As String
Dim rst As DAO.Recordset
strSQL = "Select * From [tblLabels-Export] Where [tblLabels-Export].[FamilyID]= '" & myID & "'"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
strFam = rst![Family] & " " & myID
strHolder = strHolder & rst![Name] & " ,"
rst.MoveNext
Loop
getFamily = strFam & " " & Mid(strHolder, 1, Len(strHolder) - 2)

End Function

 

[PaulBricker]

OK, the code looks fine. The SQL string shows all on one line on my monitor. Just for fun, take out the DAO reference. Make this line
Dim rst As DAO.Recordset
this
Dim rst As Recordset

Also, go into the table, tblLabels-Export, in DESIGN ViEW, and double check that the FamilyID field is set to Text.

Paul

 

[SherryLynn]

Hi Paul,

Congratulations!!! It worked beautifully. I did what you said and all worked fine. I went back into the code and replaced the DAO reference and it still worked. That should be ok to leave it right?

Now, here's my next issue (if you don't mind). This information, as I had said needs to be placed on labels. I know earlier in your posts you mentioned replacing a part of the code to put the children's names underneath the family name. Will that still work? Also, what works better - making the label report in Access or exporting this information into Word? It would be nice to keep it all in Access if possible.

Thanks again for all your help - I really appreciate it.

Sherry

 

[PaulBricker]

First, you can leave the DAO reference right where it is without problems.
Second, to move the kids names to the next line replace the last line in the code
getFamily = strFam & " " & Mid(strHolder, 1, Len(strHolder) - 2)

with this

getFamily = strFam & vbCrLf & Mid(strHolder, 1, Len(strHolder) - 2)

All you are doing is adding a carriage return and line feed to the string. Make sure you increase the height of the rows in your query so you can see both lines.

Third, you should be able to use an Access Report to do what you want. They have a Label Wizard for Access reports when you create a new Report. That will probably be your best guide.

I'm glad you got it. Code can be daunting at first but once you get a little of it under your belt, it helps to smooth out a lot of rough places in Access.

Paul

 

[SherryLynn]

Hi Paul,

Thanks very much. I have created a label report to pull this information into. One thing that I forgot in all of this is the file number portion needs to be right justified and the family last name portion left, but on the same line if possible (just for the layout of the labels when they print). Also, if there are more children's names that what would fit on the second line (under the family name), will they move onto a third line? This type of label will fit 3 lines of type normally. Any ideas?

Sherry

 

[PaulBricker]

Well I'm not much on labels. Is there some way of knowing how many characters can fit on one line of a label.
Currently, is the Family Name Left Justified the way you want, and we just need to Right Justify the ID?

Paul