Adding new records by code

[Guest]

hi gurus

I have two unbound comboboxes with fixed lists that I use to create matching
entries in a table when I click on an 'Add New' button. I'm new at this
access game, so I suspect my code for the 'Add New' button is not well
written:

Private Sub AddNew_Click()
DoCmd.GoToRecord , , acNewRec

txtClientName.SetFocus
Me.txtClientName = Me.cboClientName

txtMenuName.SetFocus
Me.txtMenuName = Me.cboMenuName

cboClientName.SetFocus

Me.Requery

End Sub

a) I have to keep changing focus to get the values to enter into the query.
How is this normally done?
b) When the form first opens, the comboboxes appear blank. How can I go
back to this state after clicking?

 

[Graham Mandeno]

Hi Cinnie

a) You do NOT have to SetFocus to a control in order to assign a value to
it. All the SetFocus lines in your code below are redundant and can be
deleted.

b) To clear a combo box, simply set its value to Null:
cboClientName = Null
cboMenuName = Null

 

[Guest]

Thanks Graham

I appreciate your help. My first attempt at code is getting so close! This
code uses the values in 2 unbound comboboxes to enter values into textboxes
on a form. But after the values are added, I can still see the little pencil
image to the left of the row. Only when I mouse-click on the new row (the
one with a *) do the values actually get added to the underlying table.
What line of code am I missing? DoCmd.Save doesn't work.

Private Sub cmdAddNew_Click()

DoCmd.GoToRecord , , acNewRec

Me.txtClientID = Me.cboClientInfo.Column(0)
Me.txtClientName = Me.cboClientInfo.Column(1)

Me.txtMenuID = Me.cboMenuInfo.Column(0)
Me.txtMenuName = Me.cboMenuInfo.Column(1)

End Sub







--
cinnie

Hi Cinnie

a) You do NOT have to SetFocus to a control in order to assign a value to
it. All the SetFocus lines in your code below are redundant and can be
deleted.

b) To clear a combo box, simply set its value to Null:
cboClientName = Null
cboMenuName = Null
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

hi gurus

I have two unbound comboboxes with fixed lists that I use to create
matching
entries in a table when I click on an 'Add New' button. I'm new at this
access game, so I suspect my code for the 'Add New' button is not well
written:

Private Sub AddNew_Click()
DoCmd.GoToRecord , , acNewRec

txtClientName.SetFocus
Me.txtClientName = Me.cboClientName

txtMenuName.SetFocus
Me.txtMenuName = Me.cboMenuName

cboClientName.SetFocus

Me.Requery

End Sub

a) I have to keep changing focus to get the values to enter into the
query.
How is this normally done?
b) When the form first opens, the comboboxes appear blank. How can I go
back to this state after clicking?

 

[John Vinson]

Only when I mouse-click on the new row (the
one with a *) do the values actually get added to the underlying table.
What line of code am I missing? DoCmd.Save doesn't work.

Save doesn't save the record - it saves *design changes to the Form
itself*.

To save the record either

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False

I must ask - why are you doing programmatically what Access does for
you automatically? It looks like you're possibly storing redundant
data from the combo box into other fields of the table; where is this
information coming from, where is it going, and Is This Trip
Necessary?

John W. Vinson[MVP]

 

[Guest]

hi John - thanks for the advice. It works well.

As to your question " ": I'm trying to use 2 unbound comboboxes (one for
Client's names and one for Menus), each row-sourced from its own list table,
to generate a new table showing matches of Clients with their preferred menu.
In general, is there a better approach? (I'm VERY new at this, so I hope
this isn't a dumb question)

 

[John Vinson]

hi John - thanks for the advice. It works well.

As to your question " ": I'm trying to use 2 unbound comboboxes (one for
Client's names and one for Menus), each row-sourced from its own list table,
to generate a new table showing matches of Clients with their preferred menu.
In general, is there a better approach? (I'm VERY new at this, so I hope
this isn't a dumb question)

You certainly do NOT need or want to create a new table, and - though
you can do so if you wich - you don't need unbound controls to do
this. If each Client can use several Menus, and each Menu can be used
by several Clients, you'ld normally treat this just as you would any
other many to many relationship: use a table of ClientMenus, with
fields for the MenuID and ClientID. You could use a Subform based on
this table on either a form of Clients (using the ClientID as the
master/child link field and a combo box to pick the MenuID), or vice
versa - a form based on Menus.

This ClientMenus table would be just a normal table in your database -
you would not need to "generate a new table".

John W. Vinson[MVP]

 

[Guest]

Yes, makes a lot of sense. Thanks for taking the time.