Use button to record combo box PK's to table

[bigalpha]

That's up to you! You design whatever you see fit for your users.

I definitely feel like it'd be easier for me to code a small entry form that opens up that allows a new inventory entry.

 

[vbaInet]

Well, that's more intuitive. But if the form that's being searched against is the inventory table alone then that same form can be used to add new entries.

 

[bigalpha]

Well, that's more intuitive. But if the form that's being searched against is the inventory table alone then that same form can be used to add new entries.

Right, but I figure that having the entry a little more blatant and intuitive would be good in this case.

edit: not that I know how to get it to enter a new line in the lookup table then add the corresponding PK to the inventory table.

 

[vbaInet]

Give it some thought and decide the approach you'll take.

 

[bigalpha]

Give it some thought and decide the approach you'll take.

Well, I'm back. I spent all day yesterday trying to get this to work with different methods to no avail.

What I want:
1. If there's text in the textbox and it does not match an existing record, then add the value from the textbox.

2. If there's no value in the text box, do nothing (add no values). [I haven't implemented this into the code]

3. If there is text in the textbox and it does match an existing record, then do nothing.

I was going to set up a block of code like this for each text box. However, it goes straight to adding the data without going through the block of validation.

Private Sub btnAdd_Click()
Dim strmessage As String

' If there's something in the text box AND it is already in the table, then don't add it

If Len(Me.txtDes & vbNullString) > 0 And DCount("[Designname]", "tblDesignName", "[Designname]='" & Me![txtDes] & "'") > 0 Then
Exit Sub
Else

SQL = "INSERT INTO tblCollection (CollectionName) VALUES ([forms]![frmAddJewelryInventory]![txtcol])"
DoCmd.RunSQL SQL

End If

End Sub

 

[vbaInet]

Conditions 1 and 3 are the same. Correct it and we can look at your code.

 

[bigalpha]

Conditions 1 and 3 are the same. Correct it and we can look at your code.

Crap.

I updated it to be correct.

What I want:
1. If there's text in the textbox and it does not match an existing record, then add the value from the textbox.

2. If there's no value in the text box, do nothing (add no values). [I haven't implemented this into the code]

3. If there is text in the textbox and it does match an existing record, then do nothing.

 

[vbaInet]

Ok. Is the criteria going to be applied to a form or a control? You want to:

1. first apply the filter the criteria to the form/control
2. test if it returns any records
3. Use that as your basis for deciding whether to add the record or not.

 

[bigalpha]

Ok. Is the criteria going to be applied to a form or a control? You want to:

1. first apply the filter the criteria to the form/control
2. test if it returns any records
3. Use that as your basis for deciding whether to add the record or not.

I have 3 unbound text boxes that the data will get entered into. The code will execute on a button click.


Ah, figured out part of this. While I was working with getting the code to work, I inadvertently mixed up references to the text boxes. I fixed that and now the code works as-is.

Can I add an OR statement to

If Len(Me.txtDes & vbNullString) > 0 And DCount("[CollectionName]", "tblCollection", "[CollectionName]='" & Me![txtCol] & "'") > 0 Then

to evaluate for empty fields?

 

[vbaInet]

You're missing my point. Those textboxes are search textboxes used to eventually filter a form right?

 

[bigalpha]

You're missing my point. Those textboxes are search textboxes used to eventually filter a form right?

Uhhhh, sorry. I was continuing my previous thought on adding data. Sorry, I should have specified. I created a separate form to do the data entry in because that's more intuitive for the end user, IMO.

So this block of code is only for adding data to the tblCollection, tblDesignName and tblJewelryType from the separate entry form.

 

[vbaInet]

Something like this:

    Dim blCondition As Boolean
    Dim strCriteria As String
    
    blCondition = Len(Me.txtColId & vbNullString) <> 0 And _
                  Len(Me.txtDesId & vbNullString) <> 0 And _
                  Len(Me.txtJewId & vbNullString) <> 0
    
    If blCondition = True Then
        strCriteria = "[CollectionId] = " & Me.txtColId & " AND " & _
                      "[DesignId] = " & Me.txtDesId & " AND " & _
                      "[JewelleryTypeId] = " & Me.txtJewId
        
        If DCount("[CollectionName]", "tblCollection", strCriteria) <> 0 Then
            
        End If
    End If

... you should be using the numeric Id not the description.

 

[bigalpha]

Something like this:

    Dim blCondition As Boolean
    Dim strCriteria As String
    
    blCondition = Len(Me.txtColId & vbNullString) <> 0 And _
                  Len(Me.txtDesId & vbNullString) <> 0 And _
                  Len(Me.txtJewId & vbNullString) <> 0
    
    If blCondition = True Then
        strCriteria = "[CollectionId] = " & Me.txtColId & " AND " & _
                      "[DesignId] = " & Me.txtDesId & " AND " & _
                      "[JewelleryTypeId] = " & Me.txtJewId
        
        If DCount("[CollectionName]", "tblCollection", strCriteria) <> 0 Then
            
        End If
    End If

... you should be using the numeric Id not the description.

The reason I used the description is because the entry is a text box; so the code would look at what you typed in it. Because this data is getting stored in the lookup table(s), I didn't think I'd need the ID's.

 

[vbaInet]

Makes sense, I was thinking they were combo boxes at the time of writing the code. Change as you see fit.

 

[bigalpha]

Thanks again for your help!

 

[vbaInet]

No problemo!