Adding info to a combo box that is dependant on another combo box

[snub]

I have an inventory control database that allows me to add and sell products. On my frmAddNewProducts form it asks for Manufacturer and ProductName, these are in combo boxes. The combo boxes are linked so that the cboProductName box is dependant on the cboManufacturer box, this works well. I would like to set it up so that I can add new products that are not currently in the list, I have a tblManufacturers and a tblProductName that are feeding the combo boxes. I have code in place now that will allow me to add the new products but I can't get the two features (adding new products and the second combo box being dependant on the first) to work together. Any suggestions. I searched the FAQ's and couldn't find this scenario.

 

[TheAceMan1]

How are ya snub . . .

[ol][li]Is the [blue]primarykey[/blue] of tblManufacturers [blue]included in the RowSource[/blue] property for Combo2?[/li]
[li]Relationships should be:
[ol a][li]tblManufacturers [blue]one side[/blue].[/li]
[li]tblProductName [blue]many side[/blue].[/li]
Is this correct?[/ol][/li]
[li]Quote snub: [blue]I have code in place now that will allow me to add the new products . . .[/blue][/li]
Post the code!
[/ol]
If things are set properly, it should be as easy as an [blue]append query/sql[/blue] along with a [blue]requery[/blue] of combobox2!

Your thoughts? . . .

See Ya! . . . . . .

 

[snub]

AceMan,

Here is the Row Source for the second combo box: SELECT tblProductName.ProductId, tblProductName.Product, tblProductName.ManufacturerId FROM tblProductName WHERE (((tblProductName.ManufacturerId)=Forms!frmAddNewVehicle!cboManufacturer)) ORDER BY tblProductName.Product;

The Primary Keys for tblManufacturers are ManufacturerID and Manufacturer


The code I'm using to add new info is:

Private Sub cboManufacturer_NotInList(strNewData As String, _
intResponse As Integer)
'Set Limit to List property to Yes


On Error GoTo ErrorHandler

Dim intResult As Integer
Dim strTitle As String
Dim intMsgDialog As Integer
Dim strMsg1 As String
Dim strMsg2 As String
Dim strMsg As String
Dim cbo As Access.ComboBox
Dim strTable As String
Dim strEntry As String
Dim strFieldName As String

'The name of the look-up table
strTable = "tblManufacturer"

'The type of item to add to the table
strEntry = "Manufacturer"

'The field in the look-up table where the new entry is stored
strFieldName = "Manufacturer"

'The add-to combo box
Set cbo = Me![cboManufacturer]

'Display a message box asking if the user wants to add
'a new entry
strTitle = strEntry & " not in list"
intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
strMsg1 = "Do you want to add "
strMsg2 = " as a new " & strEntry & " entry?"
strMsg = strMsg1 + strNewData + strMsg2
intResult = MsgBox(strMsg, intMsgDialog, strTitle)

If intResult = vbNo Then
'Cancel adding the new entry to the look-up table
intResponse = acDataErrContinue
cbo.Undo
Exit Sub
ElseIf intResult = vbYes Then
'Add a new record to the look-up table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTable)
rst.AddNew
rst(strFieldName) = strNewData
rst.Update
rst.Close

'Continue without displaying default error message
intResponse = acDataErrAdded

End If

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub

Like I said, if I only use the above code to allow data to be entered it works fine. If I remove this code and have the cboProductName dependant on the cboManufacturer then that works but when I try to combine I can't add the new data. The NotInLinst code is taken from the cboManufacturer it's the same code for the second combo box just edited for that field. The error I get when trying to add new data is "The text you entered is not an item in the list" Select and item ... OK

Thanks

 

[TheAceMan1]

snub . . .

Yourt main problem appears to be with [blue]inclusion of foreignkeys[/blue] when you add a record. You say:

[blue]The Primary Keys for tblManufacturers are [purple]ManufacturerID[/purple] and [purple]Manufacturer[/purple][/blue]

Are you saying [purple]ManufacturerID/Manufacture[/purple], is a compound PK?

See Ya! . . . . . .

 

[snub]

Yes, it is a compound primary key. Should I make only ID the primary key? If so, why is it important to be designed that way?

 

[TheAceMan1]

[blue]Should I make only ID the primary key? If so, [purple]why is it important to be designed that way?[/purple][/blue]

Its not that its important to be disigned this way, as much as the compound key is not necessary. Compound keys have their place, but not here.

Consider the PK logic for tblManufacturers. A PK here should [purple]uniquely identify[/purple] a manufacturer . . . wether its a compound ten key, or single key PK. The [purple]Idea is, why do with many what you can do with the one![/purple] This is the over complication that arises when using compound keys. Any time you involve yourself in this type of PK, you have to take account of all the keys in the mix, particularly when assigning new keys (instead of assigning one, you'll have to assign two or more). [purple]Its a matter of making it easier on yourself![/purple]

So yes . . . I'd make ID the singular PK for table tblManufacturers. Its simple, easily unique, fits the logic of the table, and most importantly . . . gives you [blue]ease of manipulation[/blue]. Always bear in mind, PK's are what access needs to identify records.

Getting back to the problem, this is what you have when the user answers yes:

[blue]   ElseIf intResult = vbYes Then
      [green]'Add a new record to the look-up table[/green]
      Set dbs = CurrentDb
      Set rst = dbs.OpenRecordset(strTable)
      rst.AddNew
      rst(strFieldName) = strNewData
      rst.Update[/blue]

[ol][li]Your adding to the wrong table (should be [blue]tblProductName[/blue]).[/li]
[li][purple]Note: no FK (Foreign Key) is assigned![/purple].[/li]
[li]Considering your compound key, you may have to [blue]add the key Manufacturer[/blue].[/li]
[li]Note: if the [blue]PK[/blue] of [blue]tblProductName[/blue] is not autonumber, it'll have to be appended as well![/li][/ol]
Get the picture with the missing key(s)! . . . take care of the PK/FK when you add and your problem is solved . . .

Considering you should have:
[ol][li]A one to many relationship between tblManufacturers & tblProductName.[/li]
[li] ManufacturerID is the single PK of tblManufacturers.[/li]
[li]ProductID of tblProductName is autonumber.[/li][/ol]
This is what you should have:

[blue]Private Sub cboManufacturer_NotInList(strNewData As String, _
                                      intResponse As Integer)
   Dim db As DAO.Database, rst As DAO.Recordset
   Dim Msg As String, Style As Integer, Title As String, SQL As String
   
   Msg = "Do you want to add '" & strNewData & "' " & _
         "as a new Manufacturer entry?"
   Style = vbYesNo + vbExclamation + vbDefaultButton1
   Title = "Product Name Not In List! . . ."
   
   If MsgBox(Msg, Style, Title) = vbYes Then
      Set db = CurrentDb
      Set rst = dbs.OpenRecordset("tblProductName")
      Set CBx = Me!cboManufacturer
      
      rst.AddNew
      rst!Manufacturer = strNewData
      rst!ManufacturerID = Me!cboManufacturer
      rst.Update
      intResponse = acDataErrAdded
      
      Set rst = Nothing
      Set db = Nothing
   Else
      intResponse = acDataErrContinue
      Me!cboManufacturer.Undo
   End If
   
End Sub[/blue]

[purple]Your thoughts . . .[/purple]

See Ya! . . . . . .