Manipulating data from a field with multi-valuable

Question

I have a table with a field containing multi-valuable as shown below: In the form, I want to let the user enter a NCR_Num in the textbox then using VBA to do some input validation then add it to the "text_Pool" as shown below:

This Text_Pool has the NCR_Num as the control source so if there is a NCR number added or deleted from it, it will automatically update the NCR_Num field.

I am not quite sure how to handle this data type. In VBA, I cannot obtain the value from the Text_Pool because I think I need to treat it as an array or recordset

Below is an example of me trying the recordset attempt but obviously I am quite confused on what I am doing:

Public Function get_NCR_Num(SCAR_Num As Integer) As Integer()
Dim dbsMain As DAO.Database
Dim rstMain As DAO.Recordset
Dim childRS As Recordset

Dim sSearchField, sCriteria As String

Set dbsMain = CurrentDb
Set rstMain = dbsMain.OpenRecordset("tbl_SCAR", dbOpenDynaset, dbReadOnly)
Set childRS = rstMain!NCR_Num.Value

sSearchField = "[SCAR_Num]"
sCriteria = sSearchField & " = " & [SCAR_Num]

With rstMain
    .MoveLast
    .FindFirst (sCriteria)

    With childRS
        Do While (Not .EOF)
             MsgBox (childRS!NCR_Num.Value)
             .MoveNext
        Loop
    End With

End With

rstMain.Close
dbsMain.Close
Set rstMain = Nothing
Set dbsMain = Nothing
End Function

Any help will be appreciated!

I'm not sure if you want to search field 'SCAR_Num' or 'NCR_Num'? Your example doesn't show the 1 or 6 as part of 'NCR_Num'. Are the NCR_Num's always four characters? If so, you can simply use a query for your recordset, then use wildcard character (i.e. Select * from tbl_SCAR where NCR_Num like '*3010*'; If not exactly four characters, your VBA code could make sure the string was either at the start or the end and/or delimited by a ',' or space. — Wayne G. Dunn, Apr 12 '16 at 22:03
Can you normalize the database itself? That seems like it would be a more robust solution. — Comintern, Apr 12 '16 at 22:58
@WayneG.Dunn 1 and 6 is just an example. The data validation that I was mentioning in my question for example would be checking if a NCR number is existed in the database. — Jason Chan, Apr 13 '16 at 15:44
@WayneG.Dunn yeah, you search for the autonumber first then you can access its related field (for example, NCR_Num). Thats just my way ususally doing it =P — Jason Chan, Apr 13 '16 at 16:52
@WayneG.Dunn basically I want to verify if that NCR_Num existed first then establish the relationship between The NCR an SCAR table(Their other fields does not matter) and show it on the textPool at the same time. — Jason Chan, Apr 13 '16 at 21:16
@WayneG.Dunn yes your logic is right. I have a SCAR table and NCR table. NCR table has it own NCR_Num(AutoNumber) and SCAR_Num(Related to the AutoNumber in SCAR table. Vice versa for SCAR. Hope this will clear the confusion — Jason Chan, Apr 14 '16 at 18:02
@WayneG.Dunn but tbh, I am only stuck on how to store a value to the multivaluable field — Jason Chan, Apr 14 '16 at 18:04
(1) yes (2) not sure your question (3)confirm if 6 existed in NCR_table->show on the textpool and add it to the compound field (4) better to add in sequence — Jason Chan, Apr 14 '16 at 20:17
To clarify your query about normalisation (or at least the bit that @Comintern was talking about): http://dba.stackexchange.com/questions/2342/what-is-atomic-relation-in-first-normal-form — Darren Bartrup-Cook, Apr 15 '16 at 16:03

Wayne G. Dunn · Accepted Answer · 2016-04-15T15:33:52.347

I misunderstood your question, and have updated the answer with the following code. This should do what you want. Replace the code you have in subroutine 'Command_LinkNCR_Click' with the following. This will: (a) validate nbr exists; (b) add if not present; (c) remove if present;

WARNING!! This code only addresses the one issue you were trying to overcome. However, it makes an update of the same recordset as you are viewing on the form, so there may be an issue if your form is 'Dirty'.

Give this a try and let me know if you have questions.

Private Sub Command_LinkNCR_Click() Dim dbs As DAO.Database Dim rsMain As DAO.Recordset Dim rsChild As DAO.Recordset Dim strSQL As String Dim blnMatch As Boolean

If IsNull(Me.Text_NCR) Or Me.Text_NCR = "" Then
    MsgBox "No value entered for NCR_Num", vbOKOnly, "Missing Value"
    Exit Sub
End If

blnMatch = False
Set dbs = CurrentDb
' Only need to work on the current record
strSQL = "select * from tbl_SCAR where SCAR_Num = " & Me!SCAR_Num & ";"
Set rsMain = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rsMain.EOF Then
    ' Should never happen
Else
    Set rsChild = rsMain!NCR_Num.Value
    If rsChild.EOF Then     ' If no values yet, add this new one
        MsgBox "Add item"
    Else
        Do While Not rsChild.EOF
            ' See if we have a match...
            If Int(rsChild.Fields(0)) = Int(Me.Text_NCR) Then
                blnMatch = True
                rsChild.Delete                  ' Delete item
                Exit Do
            End If
            rsChild.MoveNext
        Loop
        If blnMatch = False Then            ' Need to Add it
            rsMain.Edit
            rsChild.AddNew
            rsChild.Fields(0) = Me.Text_NCR
            rsChild.Update
            rsMain.Update
        End If
    End If

End If

'rsChild.Close
rsMain.Close
dbs.Close
Set rsMain = Nothing
Set rsChild = Nothing
Set dbs = Nothing

Me.Refresh

End Sub

Sorry, I have been away from holiday. I think I will create another button to let the user delete so it looks more user-friendly. But generally speaking this is great and clear my confusion on how to use rschild! Thanks for your help Wayne and I think I can use your great help to apply to other functions in the database! — Jason Chan, Apr 18 '16 at 14:07

Manipulating data from a field with multi-valuable

1 Answers1