How concatenate into one field?

[SteveL]

I have a form named "frmJobCards" which is bound to a table named "tblPartsMasters" On the form is a listbox named "List1". This listbox is bound to a table named "tblCbxNumberData". Each record in the table named "tblPartsMasters" can have more than one associated record in "tblCbxNumberData". In other words, a one to many relationship. Here is my question... on the frmJobCards I want to have a field named "txtCombinedCbxRecordInfo" which will be bound to a field in the table "tblPartsMasters" named "CombinedCbxRecordInfo". When the forms is opened and a record selected to the form, I need this new field to contain all of the records in the field "CoreSandType" from "tblCbxNumberData" populated to the field. In other words, lets say record #2 in tblPartsMasters" has 2 records linked to it in "tblCbxNumberData" and the 2 records have "White" and "Black" entered in "CoreSandType" from "tblCbxNumberData. When I go to record #2 in "frmJobCards" I want the new field "txtCombinedCbxRecordInfo" to populate with "White/Black" (Note "/" separator).

Any help will sure be appreciated.

 

[MarkK]

I would do something like this. I'm not sure what the names are of your primary or foreign keys, so you'll have to change these in the SQL that opens the recordset...

private sub form_current()
  dim rst as dao.recordset
  dim txt as string

  if not me.newrecord then
    [COLOR="Green"]'open a recordset on the child records of the current record in Me[/COLOR]
    set rst = currentdb.openrecordset( _
      "SELECT CoreSandType " & _
      "FROM tblCbxNumberData " & _
      "WHERE [COLOR="DarkRed"]frmJobCardsPK = " & tblCbxNumberDataFK[/COLOR])
    with rst
      do while not .eof
        [COLOR="green"]'if the field is non-null, concatenate[/COLOR]
        if not isnull(.fields(0)) then txt = txt & .fields(0) & "/"
        .movenext
      loop
      .close
    end with
    [COLOR="green"]'drop the trailing "/"[/COLOR]
    if txt <> "" then txt = left(txt, len(txt) - 1)
  end if
  
  me.txtCombinedInfo = txt

end sub

 

[SteveL]

Here is my code so far but it isn't working. Can you spot what I am doing wrong? (copy and pasted below)

Dim rst As dao.Recordset
Dim txt As String

If Not Me.NewRecord Then
'open a recordset on the child records of the current record in Me
Set rst = CurrentDb.OpenRecordset( _
"SELECT Core Sand Type " & _
"FROM tblCbxNumberData " & _
"WHERE frmJobCards.txtPartN = " & tblCbxNumberData.PartN)
With rst
Do While Not .EOF
'if the field is non-null, concatenate
If Not IsNull(.Fields(0)) Then txt = txt & .Fields(0) & "/"
.MoveNext
Loop
.Close
End With
'drop the trailing "/"
If txt <> "" Then txt = Left(txt, Len(txt) - 1)
End If

Me.txtCombinedCbxRecordInfo = txt

 

[pbaldy]

If the field name really contains the inadvisable spaces, it would need to be bracketed.

 

[SteveL]

Ok. Here again is what I have so far but still isn't working...

Dim rst As dao.Recordset
Dim txt As String

If Not Me.NewRecord Then
'open a recordset on the child records of the current record in Me
Set rst = CurrentDb.OpenRecordset( _
"SELECT [Core Sand Type] " & _
"FROM tblCbxNumberData " & _
"WHERE me.txtPartN = " & [PartN])
With rst
Do While Not .EOF
'if the field is non-null, concatenate
If Not IsNull(.Fields(0)) Then txt = txt & .Fields(0) & "/"
.MoveNext
Loop
.Close
End With
'drop the trailing "/"
If txt <> "" Then txt = Left(txt, Len(txt) - 1)
End If

Me.txtCombinedCbxRecordInfo = txt

 

[pbaldy]

Define "isn't working". You get an error, unexpected results, etc. Have you set a breakpoint and stepped through the code?

 

[SteveL]

When I select a record in the form, frmJobCards, nothing is being filled in in the field on the form named "txtCombinedCbxRecordInfo".

Secondly, I wish I knew more about breakpoints, stepping into, etc. I don't even know where to begin.

 

[pbaldy]

Here are some links on debugging:

http://www.mdbmakers.com/forums/showthread.php?t=2735
http://www.mdbmakers.com/forums/showthread.php?t=2756
http://www.mdbmakers.com/forums/showthread.php?t=2737

If you can post a sample db, perhaps one of us will see the problem more easily.

 

[MarkK]

A few things.
- Where is this code you're posting? Are you sure it's in the Current event handler for the form?
- To check if it actually runs, stick a msgbox('Got Here') in it somewhere and see if the message box pops up.
- Break into running code, including when a msgbox is modal, using <ctrl>+<break> and *poof* you're debugging.
- Set a breakpoint in code by clicking in the left margin. Click around til you get a redish circle in the margin and the line is highlighted. Now, when code execution gets there it stops, maybe highlighted in yellow. click the arrows on your Debug toolbar, to advance the code one line at a time.
- The WHERE clause in your SQL can't have the word Me in it. Keep in mind that SQL is a whole different language from VBA, so all the stuff in quotes where you open that recordset has it's own syntax.
- Your WHERE clause may also be doing a string comparision, in which case it needs quotes.

"WHERE strTableField = '" & Me.strFormField & "'"

- Lemme know...You gotta be very close...

 

[SteveL]

I have tried everything I can think of. So, one more time... here my my entire code block from the on current event. Could there be a conflict of some sort between the first block of code which I've had all along and this new block of code to attempt to accomplish the current desired result?
-=-=-=-=-
'for State Line purposes
If Len(Dir("\\Sbs1\Company\General Shared Files\PRINTS\" & Me.txtPartN & "\*.*")) = 0 Then
Me!chkprints = False
Else
Me!chkprints = True
End If
'end of for stateline purposes

Me.List1.Requery

Me.txtCPC = DSum("CPC", "tblCbxNumberData", "(PartN = '" & Me.txtPartN & "')")
Me.txtCBs = DCount("CPC", "tblCbxNumberData", "(PartN = '" & Me.txtPartN & "')")



' Code to fill combine field with concatenated text
Dim rst As dao.Recordset
Dim txt As String

If Not Me.NewRecord Then
'open a recordset on the child records of the current record in Me
Set rst = CurrentDb.OpenRecordset( _
"SELECT [Core Sand Type] " & _
"FROM tblCbxNumberData " & _
"WHERE cboPartN = '" & PartN & "'")

With rst
Do While Not .EOF
'if the field is non-null, concatenate
If Not IsNull(.Fields(0)) Then txt = txt & .Fields(0) & "/"
.MoveNext
Loop
.Close
End With
'drop the trailing "/"
If txt <> "" Then txt = Left(txt, Len(txt) - 1)
End If

Me.txtCombinedCbxRecordInfo = txt
' End of code to fill combine field with concatenated text

 

[Rich]

For starters you shouldn't store the result of what is a calculated field, save the function in a new module and use it in a query

 

[SteveL]

I do not know how to do what you are suggesting. Sorry. Can you assist?

 

[MarkK]

Just posting code and saying it doesn't work is not enough information. How do you know it's not working, or what about it doesn't work? Do you not get the expected result? Do you get an error? There are cases this code will run correctly and will produce no result. How do you know that isn't happening?