Dynamic Concatenate Function in SQL

[alsoascientist]

Hi All,

I've not tried to use a function when building an expression before so please bear with me if I am missing something obvious!

I have the following function which is placed in a query to return a concatenated string depending on which comboboxes are currently visible on the form (the idea being to fire a code on the next record button to check for a duplicate of the entire text string).

I seem to be having two issues with it:
1. The code seems to build twice the data that is needed ie [FIELD1]&[FIELD2]&FIELD3][FIELD1]&[FIELD2]&FIELD3] (no '&' in between the two sets) and I can't figure out why. It doesn't do this when I check it by stepping through (that I can see) but does when it is fired from the query. This I can kind of fix by dividing the text in two (as in the code) but I'm not sure if this may be the root of my issue.

2. The function does not return any data. Again if I step through and copy the information from the immediate window and place it in the query I can get the desired information, however if I allow it to work itself I get nothing!

Any ideas where I should be looking?

Public Function DuplicateStrChk()
Dim Ctrl As Control
Dim strDup As String
        strDup = ""
        For Each Ctrl In Forms![myForm].Controls
        If Ctrl.ControlType = acComboBox Then
        If Ctrl.Visible = True Then
        strDup = strDup & "[" & Ctrl.Name & "]&"
        End If
        End If
        Next
 
        If Right(strDup, 1) = "&" Then
        strDup = Left(strDup, Len(strDup) - 1)
        strDup = Left(strDup, Len(strDup) / 2)
        strDup = strDup
        End If
End Function

 

[alsoascientist]

Ok so I realised that I had the str = "" commented out which is why it was creating a double string (i think) but I'm still not getting any data in the query.

 

[spikepl]

Look up the documentation or some tutorial for how to make a function in VBA and return a value from it.

The intent of your undertaking is not clear either.

You probably intend to get [FIELD1]&[FIELD2]&FIELD3] but a function will yield "[FIELD1]&[FIELD2]&FIELD3]" - a STRING! You cannot pass SQL language elements - such as field names - to a running query and thus modify it on the fly, only values. An entire SQL-statement can be constructed in VBA and run there, or one can modify a query's SQL using the QueryDef - google it on this site or elsewhere.

 

[jdraw]

Perhaps you should tell us in plain English what exactly you want to so. It would even be more productive if you could use a simple example to illustrate the input and what you would like returned from the function.

 

[Pat Hartman]

You never assigned a value to DuplicateStrChk so it had nothing to return. Add the following to the end of the procedure.

DuplicateStrChk = strDup

 

[alsoascientist]

Thanks Pat, jdraw, spikepl,

Pat and spike yeah - you are both right - when I assign the string to DuplicateStrChk it will return the actual string for each record in the query where I am looking for the result of the string (I did at one point manage to get the result of it but it was the same result for each record which wasn't right!)

jdraw - What I am trying to do is this:
I have a number of field in the database that are driven from a table that notes them as either active or inactive.
I need to be able to find where entire records that are entered are duplicates so the plan is to create a string of all the active field values for each record and the one being input and check for duplicates in this new field on the on click event of the 'next record' button that I have built in. This would re-query the previous records fields so that if any changes have been made to the layout to the fields this would still be picked up.

I am attempting to build a query on the event (I already have one that builds the SELECT as only the visible fields) but I am really struggling with adding the expression to the query to get the concatenation. I've seen some information sayin I should use the concat() function (I'm not sure if this is a function itself or I should build a concat function and then use it?).

I'm thinking:
1.build a new query (each time)
2.add the concat function (after so that it has something to look up against)
3.check the field against the others

does this sound about right?
are there any easier ways?!

 

[jdraw]

I'm not following your description, I'm afraid. As I mentioned, a sample of what you are doing with real data may help.

You may be able to prevent/detect duplicates duplicates by using DCount().
There is a Concat Related function at http://allenbrowne.com/func-concat.html

 

[alsoascientist]

Thanks again, jdraw

I did have a play around with this one earlier on in trying to figure this out but had disregarded it as not being fit for purpose, however looking at it again with wiser eyes (like I said I've not tried anything like this before!) I think this may be exactly what I'm after!

Essentially what I want to do is:

User selects field 1,2,4,5 as visible from the sperate form controling this

enters:
Field1 - abc
Field2 - def
Field4 - ghi
Field5 - jkl

there is a field already entered as:
Field1 - abc
Field2 - def
Field4 - ghi
Field5 - jkl

query conatenates both and does a dcount and recognises the duplicate:
record 1: abcdefghijkl
record 2: abcdefghijkl

but if the visible fields are changed to 1,2,3,4,5

enters:
Field1 - abc
Field2 - def
Field3 - xyz
Field4 - ghi
Field5 - jkl

there is a field already entered as:
Field1 - abc
Field2 - def
Field3 - (nothing)
Field4 - ghi
Field5 - jkl

query conatenates both and does a dcount and does not recognise the duplicate:
record 1: abcdefxyzghijkl
record 2: abcdefghijkl

then when the user goes back to fill in xyz in the second record it will re-concatenate to show abcdefxyzghijkl

I don't think I'm explaining this the best but unfortunately I am not able to upload/email any attachments

 

[jdraw]

Can we get to concrete examples?

What is the form based on? Are you displaying records already in a table?

What exactly does a "User selects field 1,2,4,5 as visible from the sperate form controling... " mean?

 

[alsoascientist]

Sorry for taking ages to get back - I keep getting sidetracked!

I've given the code from the link a good going over and can't get it to work how I want it - maybe its actually not quite what I wanted or maybe its just cos i think I'm in a bit over my head on this one!

Anyhoo... I've taken a different approach thinking about jdaw's comment about dcount think I have figured it out!

I'm now creating a query from the visible records each time it is changed and counting the number of records in the query. This works perfectly for most of my forms where the event is only fired from a double click event on a dataset, however I have a data entry form where I was trying to get this to fire on the before update event and I kep getting an error message.

Essentially,
User inputs data in a field that woudl create a duplicate record
Query is built from the visible field and the records counted
If its more then 1 a warning should pop up and a button o the form should become visible
User clicks the button and another form pops up with the same fields but with its record source set as the new query for the user to update the information.

I keep getting the error:
the macro or function set to the BeforeUpdate or validationrule property for this field is preventing from saving data

it says it is on the build query part, however if I take out the DupStop part then it works fine.

Any ideas?

Private Sub Form_BeforeUpdate(Cancel As Integer)
    'Takes the information to build the recordsource from the previous functions
        IDQry = "SELECT DISTINCT * FROM [Pivot] WHERE " & SrchWhere
    'Creates the query
        DoCmd.DeleteObject acQuery, "PivotQuery"
        Set qdf = CurrentDb.CreateQueryDef("PivotQuery", IDQry)
        If DCount("ID", "PivotQuery") = 1 Then GoTo DupFinish
    'This will run if there are duplicates, stopping the user from closing the form
DupStop:
        Me.DuplicateLBL.Visible = True
        Me.DuplicateBTN.Visible = True
        MsgBox "You are either trying to create a duplicate record or have not addressed one already there." _
        & (Chr(10)) & (Chr(10)) & "Please review the data or set one as inactive." _
        & (Chr(10)) & (Chr(10)) & "You can use the Show Duplicates button to review all matching records.", _
        vbOKOnly + vbExclamation, "Duplicates Found"
        Cancel = 1
        Exit Sub
    'This will run if there are no duplicates, checking which form is open, and allow the user to close the form
DupFinish:
        If CurrentProject.AllForms("SearchForm").IsLoaded = True Then Forms![SearchForm]![SearchSubForm].Form.Requery
        If CurrentProject.AllForms("ReportsForm").IsLoaded = True Then Forms![ReportsForm]![SearchSubForm].Form.Requery
        If CurrentProject.AllForms("RecordsForm").IsLoaded = True Then Forms![RecordsForm]![AllQuerySubform].Form.Requery
        Exit Sub
End Sub
 
Private Function SrchWhere()
srch = Null
For Each ctrl In Me.Controls
If ctrl.ControlType = acTextBox Then
'If ctrl.ColumnHidden = False Then
If ctrl.Visible = True Then
If Not ctrl.Name = "SFN18" Then
If Not ctrl.Name = "SFN19" Then
If Not ctrl.Name = "SFN20" Then
If Not ctrl.Name = "SFN21" Then
If Not ctrl.Name = "SFN23" Then
If Not ctrl.Name = "SFN24" Then
'If IsNull(ctrl.Value) Then Srch = Srch & "([" & ctrl.Name & "] Is Null OR [" & ctrl.Name & "] = '') AND "
'If IsNumeric(ctrl) Then Srch = Srch & "[" & ctrl.Name & "] = " & ctrl.Value & " AND "
srch = srch & "[" & ctrl.Name & "] = '" & ctrl.Value & "' AND "
End If
End If
End If
End If
End If
End If
End If
End If
Next
If Right(srch, 5) = " AND " Then
srch = Left(srch, Len(srch) - 5)
End If
SrchWhere = srch
End Function

 

[alsoascientist]

ok for anyone who was interested (not that I'm too sure anyone was really that clear on what I was doing!) this is now working for me
I ended up taking apart the entire module as I blieve it was actually one of the other codes I had floating about in there that was working against this one.
Just in case - here's the full code to check for duplicates in a variable number of fields, depending on the field's visible property.

Private Sub DupCheck()
    'Takes the information to build the recordsource from the previous functions
        IDQry = "SELECT DISTINCT * FROM [Pivot] WHERE " & SrchWhere
    'Creates the query
        DoCmd.DeleteObject acQuery, "PivotQuery"
        Set qdf = CurrentDb.CreateQueryDef("PivotQuery", IDQry)
        If DCount("ID", "PivotQuery") = 1 Then GoTo DupFinish
    'This will run if there are duplicates, stopping the user from closing the form
DupStop:
        'shows a label and a button to show the duplicates in a pop up form - returns to the form as this is added to the save, next etc button codes
        Me.DuplicateLBL.Visible = True
        Me.DuplicateBTN.Visible = True
        MsgBox "You are either trying to create a duplicate record or have not addressed one already there." _
        & (Chr(10)) & (Chr(10)) & "Please review the data or set one as inactive." _
        & (Chr(10)) & (Chr(10)) & "You can use the Show Duplicates button to review all matching records.", _
        vbOKOnly + vbExclamation, "Duplicates Found"
        Exit Sub
    'This will run if there are no duplicates, checking which form is open, and allow the user to close the form
DupFinish:
        If Me.DuplicateLBL.Visible = True Then Me.DuplicateLBL.Visible = False
        If Me.DuplicateBTN.Visible = True Then Me.DuplicateBTN.Visible = False
        If CurrentProject.AllForms("SearchForm").IsLoaded = True Then Forms![SearchForm]![SearchSubForm].Requery
        If CurrentProject.AllForms("ReportsForm").IsLoaded = True Then Forms![ReportsForm]![SearchSubForm].Requery
        If CurrentProject.AllForms("RecordsForm").IsLoaded = True Then Forms![RecordsForm]![AllQuerySubform].Requery
        Exit Sub
End Sub
 
Private Function SrchWhere()
srch = Null
For Each ctrl In Me.Controls
If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then
If ctrl.Visible = True Then
If Not ctrl.Name = "ID" Then
If Not ctrl.Name = "SFN18" Then
If Not ctrl.Name = "SFN19" Then
If Not ctrl.Name = "SFN20" Then
If Not ctrl.Name = "SFN21" Then
If Not ctrl.Name = "SFN23" Then
If Not ctrl.Name = "SFN24" Then
If ctrl.Tag = "Used" Then
srch = srch & "[" & ctrl.Name & "] = '" & ctrl.Value & "' AND "
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
Next
If Right(srch, 5) = " AND " Then
srch = Left(srch, Len(srch) - 5)
End If
SrchWhere = srch
End Function
'----------------------------------------------------------------------------------------------
 
Private Sub DuplicateBTN_Click()
        DoCmd.OpenForm "RecordsPopUp", , , SrchWhere
End Sub