Finally am where I was now I I am having saveing probs

[Wornout]

Have my recipe form(RecipesTbl )
I have 2 unbound combo boxs that work! and are connected to separate sub-forms
Then I have 2 text boxs taken from the fields list in recipes form aka recipes table
Everything works fine but
the combo boxs save to one row and the text boxs save to another row both in my recipes table, They are supposed to save all in the one row
It has taken me all day to get the sub forms and combo boxs to work just the way I want them to so am very reluctant to change them hoping it is the text boxs or a VBA code in them

[zmbd]

For the comboboxes, as unbound (the control source property is blank/null/zerolength) it will be code behind these controls, or the code that takes the values from these controls, that you will need to take a look at to determine what and how to alter in order to save to the same record as the the two bound text-boxes.

Please post the code.
Please remember to select the code and format it using the [CODE/] formating button (^-^)

-z

[Wornout]

Ok here goes I amuse you mean the code I have put there.
I am useing Access 2007, I have tables, [Base] (Which holds the flavour1, ID),[Base recipe](Which holds the recipe a long with RID(auto number)recipeID )[Recipes]( which holds Flavour,Flovour 1,Type,size,Bas e ID),[Recipe Ingredients],Recipe-ID(auto number)recipeID ), [Ingredients] (which holds all the ingredients in stock) Base Recipe and The Bace recipes and Recipe Ingredients are union joined.

On the form Called Recipes I have a combo box unbound called flavour1 this is its row source

SELECT [Base].[ID], [Base].[Name] FROM Base ORDER BY [Name];

Bound column 2 limit to list yes Afterupdate,

Private Sub Flavour1_AfterUpdate()
Combo110 = Flavour1.Column(0)
End Sub

(combo110 is a combo box on the subform which is taken from Base recipes Ingredients and iis connected to Flavour1 by Master ID Child Base RecipeID.Then not in list

Private Sub Flavour1_NotInList(NewData As String, Response As Integer)
Dim strTmp As String
    
    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new Name?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
    
        'Append the NewData as a record in the Base table.
        strTmp = "INSERT INTO Base (Name ) " & _
            "SELECT """ & NewData & """ AS Name;"
        DBEngine(0)(0).Execute strTmp, dbFailOnError
        
        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
    End If
End Sub

This is so I can add to the list and it gets saved t[basetbl]

Then there is the 2nd combo ,Combo131 the row source is

SELECT Recipes.Flavour, Recipes.RecipeID FROM Recipes;

Bound column 2 limit to list yes,It has another sub form Master combo131 child RecipeID,
it has in the afterupdate event

Private Sub Combo131_AfterUpdate()
IngredientID = Combo131.Column(0)
End Sub

(IngredientID is a combo bo on the sub form)which is taken from Recipe ingredients tbl.
The not in list event is

Private Sub Combo131_NotInList(NewData As String, Response As Integer)
Dim strTmp As String
    
    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new Name?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
    
        'Append the NewData as a record in the Base table.
        strTmp = "INSERT INTO Recipes (Flavour) " & _
            "SELECT """ & NewData & """ AS Name;"
         DBEngine(0)(0).Execute strTmp, dbFailOnError
        
        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
    End If
End Sub

The text box 1 are bound to Type on the recipes tbl and is where they type in if it is a pie etc
Text box 2 is bound to weight on the recipes tbl they type in the drop weight of the filling. There is no code attached to these.
These save 1st on one line under a new recipe number then the 2 combo boxs save the next line down under another number.
I want to be able to use the 1 form to add/change ingredients,var iant name, base name etc and so far apart from this problem I can do that :-).
If you can fix or help me then you are truly amazing as my description must be mind confusing Lol
PS I copied and pasted the code so it should be correct, and by doing this data base I have learned so much.

[Wornout]

I am not sure what [Code] and [/code]tag means

[zmbd]

Been a busy day... and I may not get back to this for a moment or two; however, this caught my eye on a quick glance:

On the form Called Recipes I have a combo box unbound called flavour1 this is its row source
Expand|Select|W rap|Line Numbers SELECT [Base].[ID], [Base].[Name] FROM Base ORDER BY [Name];

Bound column 2 limit to list yes Afterupdate,

I would have thought this would be bound to column 1 which is the "ID" which is often the primary key for the table...

More to follow once I read thru everything :)

[Wornout]

I think that's what I did in fact it is always open on the code I think? What I have copied and pasted is it correct I mean how you want? I don't know of any other way.
BaseTbl has ID(Auto Number) not PK for some reason I turned it off
Base RecipesTbl has BRecipeID(Auto N0) (PK) and BaseRecipeID
RecipesTbl has RecipeID(Auto No) again I turned PK off and it has ID(from Basetbl)
RecipesIngredie nts hasRecipeIngred ientID(auto number)is PK and has RecipeID and IngredientID
Thanks for taking time out of your busy day to help me I guess a little knowledge can be dangerous lol I would love to learn more I can only dream of the things I could do if I did.

Oh by the way the 2 combo boxs were bound and I could not do what I wanted to with them so I made unbound ones I forget what it was I have done so much to this DB that I can not keep track

[Wornout]

ok now I have bound my combo boxs to the right Fields and yes my sub-forms behave just like they should but my form is bound to recipes so when I alter a sub form it saves it as a whole new entry.May be you have a much better way of doing a form all I want to be able to do is bring up a base recipe(Mince or steak) it show the ingredients and I can add, edit or enter a new one,to it, the same with the variant recipe or enter a new variant recipe . The variant recipe gets saved to the recipes table along with the base recipe ID and the changes made to the base recipe get saved to the base recipe table. Maybe I should just have 2 separate forms? was trying to make it user freindly

[zmbd]

Wornout:
YEA!
Always a great feeling for me to get something working again - and more so when it's a help to someone!

As for the better design... I think we'd need to start a new thread for that as it's a seperate question. It's normally desired to limit each thread to one question to make it easier to stay on topic and more so, so that other members searching for direction can find the information.

If you do decide to start a new thread, start out with the structure of your database for example:
tblname:
[fieldname] type (text etc...) key (primary etc...) relationship and to which table.

Then a clear description of your database's goal (I think you have that in one of the other threads you started :) ) and what you have vs. what you want. Feel free to add references to the other threads you have on this project so that others can follow what you've accomplished.

[Wornout]

ok will do thanks for all your help I have never been this stuck by a data base before but I have been a bit more adventurous with this one Lol