Adding a record to a table from an input form

[Design by Sue]

I have a table with 2 fields in it - Type and Pallet_Case - using a form the user enters the name of the Type and then from a combo box selects either Pallet or Case. There is a button on the form that is to save the new record to the table. (code below) The strangeness that is happening is that when the new record is added to the table, whatever is the first existing record alphabetically in the table is changed to the same Pallet_Case selection that was made for the new record. I have a list box that displays on the form that is updated after a new record is added to show what records exist in the table (this is done by a query of the table and sorts on the type), so I can see this happening without having to open the table. It doesn't matter what sort order I put the list box in nor the table it still changes the first record alphabetically. And it is only change the Pallet–Case field. I even changed the first record to something else and it still did this. This is driving me crazy! What the heck could be doing this.

Dim dbs As DAO.Database, strSql As String
Set dbs = CurrentDb
strSql = "Insert into [PP Type TBL] ([Prepack Type],[Pallet Case]) Values ('" & Me.Type & "','" & Me.Pallet_Case & "' )"
dbs.Execute strSql, dbFailOnError

 

[bob fitz]

Sounds to me as though you are using a form with controls that are bound to a table. You open the form and it displays the data in the first record which you change. You don't need a button to save records because Access does that when you close the form or move to another button. So perhaps you just need to delete your "save" button and put some code in the forms On Open event that goes to a new record.

 

[Design by Sue]

OK now that is making sense. Thank you. I will look at it with this thought and post back shortly!

Thanks

 

[bob fitz]

You could also set the forms Data Entry property to Yes which would open the form without showing any records and present the user with a new form ready for data entry. You could even open the form and display the form like this without changing the Data Entry property by using the correct arguments in the OpenForm command that you might have in the On Click event of a button which opens the form.

 

[Design by Sue]

Duh - you were 100% correct (and you knew you were) I unbound the form and it works perfectly. (Only work on Access sporadically so I miss stuff like this - so grateful for this board and the help given.)
Sue

 

[bob fitz]

Duh - you were 100% correct (and you knew you were) I unbound the form and it works perfectly. (Only work on Access sporadically so I miss stuff like this - so grateful for this board and the help given.)
Sue

Sue
Did you see my last post. IMHO it would be better to work with a bound form, just make sure you have it working as required.