Create New Record in Form

[Dev1]

I'm triying to create a new record via a macro, like so:

MACRO NAME ACTION
CreateNewX : On Click Go To Record


Object Type : Form

Object Name: Is the name of the Form

Record: New

When i click on my 'Create New Record' button all the fields clear including my combo boxes, but when I go to put new values in the combo boxes and tab or hit return to move to the next field, the values disapear in the combo boxes and a record is not saved.


Help

[mshmyob]

Are you adding a new record to the currently opened form.

if you are then leave out the Object Type and Object Name.

I'm triying to create a new record via a macro, like so:

MACRO NAME ACTION
CreateNewX : On Click Go To Record


Object Type : Form

Object Name: Is the name of the Form

Record: New

When i click on my 'Create New Record' button all the fields clear including my combo boxes, but when I go to put new values in the combo boxes and tab or hit return to move to the next field, the values disapear in the combo boxes and a record is not saved.


Help

[Dev1]

Are you adding a new record to the currently opened form.

if you are then leave out the Object Type and Object Name.

Thank you for your response. I did try that for both macros and the problem still exists. Any other thoughts?

-Dev1

[Dev1]

Thank you for your response. I did try that for both macros and the problem still exists. Any other thoughts?

-Dev1

One quesiton should I use 'Set Value' action? for the macros that I created for those two combo boxes?

-Dev1

[mshmyob]

Just to clarify. On your form you have text boxes AND combo boxes. Do the text fields save and only the combo boxes do not save?

What macro is behind the Combo boxes??

Are the controls bound?

[Dev1]

Just to clarify. On your form you have text boxes AND combo boxes. Do the text fields save and only the combo boxes do not save?

What macro is behind the Combo boxes??

Are the controls bound?

Yes, I have text boxes and combo boxes. Yes that is correct the text fields save and only the combo boxes do not save.

I eliminated the macros and use a event for NotInList:

[CODE=vb]Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Combo83
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContin ue
ctl.Undo
End If
[/CODE]
I used this code for both of the combo box to save the new value but nothing is happening still. Oh, the combo box are not bound. I don't know how to do that yet, I'm researching that now.

Thank you for you quick response.

-Dev1

[Dev1]

Yes, I have text boxes and combo boxes. Yes that is correct the text fields save and only the combo boxes do not save.

I eliminated the macros and use a event for NotInList:

Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Combo83
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContin ue
ctl.Undo
End If

I used this code for both of the combo box to save the new value but nothing is happening still. Oh, the combo box are not bound. I don't know how to do that yet, I'm researching that now.

Thank you for you quick response.

-Dev1

I did try to bind the combox to a column in the table:

Bound Column: Company

Company is a colum in my table. When I did that it started acting very weird. I should mention also that for the Row Source I have a query that is pulling values based on preceding combox, which the first combo box has a requery in it to pull values from the table.

-Dev1

[mshmyob]

Because the combo box is not bound it will not save in your table. The code you have below is used to TEMPORARILY save your ROWSOURCE.

When you open the form again the changes will be gone.

The procedure updates the list only for the current work session. You can’t permanently update the value list in Form view. The only way to accomplish that is to open the form in Design view, update the value list, and then save the form.

Yes, I have text boxes and combo boxes. Yes that is correct the text fields save and only the combo boxes do not save.

I eliminated the macros and use a event for NotInList:

[CODE=vb]Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Combo83
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContin ue
ctl.Undo
End If
[/CODE]
I used this code for both of the combo box to save the new value but nothing is happening still. Oh, the combo box are not bound. I don't know how to do that yet, I'm researching that now.

Thank you for you quick response.

-Dev1

[mshmyob]

Weird??? A little more detail please.

Give me your settings for your 2 combo boxes.

I did try to bind the combox to a column in the table:

Bound Column: Company

Company is a colum in my table. When I did that it started acting very weird. I should mention also that for the Row Source I have a query that is pulling values based on preceding combox, which the first combo box has a requery in it to pull values from the table.

-Dev1

[Dev1]

Weird??? A little more detail please.

Give me your settings for your 2 combo boxes.

Sure,

The settings are

Combo box1:

Table/Query
SELECT [Sales Funnel1].[ASC Rep Reporting] FROM [Sales Funnel1] GROUP BY [Sales Funnel1].[ASC Rep Reporting] ORDER BY [Sales Funnel1].[ASC Rep Reporting];

Bound Column:1
Limit to list: No
Auto Expand: Yes

Event:
NotInList()

Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Combo83
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContin ue
ctl.Undo
End If


Combo Box2

Table/Query
SELECT DISTINCT [Sales Funnel1].Company FROM [Sales Funnel1] WHERE ((([Sales Funnel1].[ASC Rep Reporting])=Forms!Opportu nity!Combo83)) ORDER BY [Sales Funnel1].Company;

Bound Column:1
Limit to list: No
Auto Expand: Yes

Event: AfterUpdate()
Private Sub Combo81_AfterUp date()
If IsNull(Me.Combo 81) Then
Me.FilterOn = False
Else
Me.Filter = "Company= """ & Me.Combo81 & """"
Me.FilterOn = True
End If
End Sub

Event: NotINList()
Dim ctll As Control

' Return Control object that points to combo box.
Set ctll = Me!Combo81
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctll.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContin ue
ctll.Undo
End If


I have one table with about 8 columns. The two combo boxes are pulling value from this one table, 'Sales Funnel1'. The rest are text fields. What I'm trying to do is I have a Command Button to create a new record. For that i use a macro with the 'GoToRecord' Action. When I click the button all the fields clear including the comboxes. I use the above code to intercept the not in list error and allow the user to procede with adding a new value in both of the combo boxes. But when I tab or hit return after putting in a new value for both comb boxes the values are removed. Now I did test the other text fields to see if they are saving values and they are so that is good.

Thanks,

-Dev1

[Dev1]

Sure,

The settings are

Combo box1:

Table/Query
SELECT [Sales Funnel1].[ASC Rep Reporting] FROM [Sales Funnel1] GROUP BY [Sales Funnel1].[ASC Rep Reporting] ORDER BY [Sales Funnel1].[ASC Rep Reporting];

Bound Column:1
Limit to list: No
Auto Expand: Yes

Event:
NotInList()

Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Combo83
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContin ue
ctl.Undo
End If


Combo Box2

Table/Query
SELECT DISTINCT [Sales Funnel1].Company FROM [Sales Funnel1] WHERE ((([Sales Funnel1].[ASC Rep Reporting])=Forms!Opportu nity!Combo83)) ORDER BY [Sales Funnel1].Company;

Bound Column:1
Limit to list: No
Auto Expand: Yes

Event: AfterUpdate()
Private Sub Combo81_AfterUp date()
If IsNull(Me.Combo 81) Then
Me.FilterOn = False
Else
Me.Filter = "Company= """ & Me.Combo81 & """"
Me.FilterOn = True
End If
End Sub

Event: NotINList()
Dim ctll As Control

' Return Control object that points to combo box.
Set ctll = Me!Combo81
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctll.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContin ue
ctll.Undo
End If


I have one table with about 8 columns. The two combo boxes are pulling value from this one table, 'Sales Funnel1'. The rest are text fields. What I'm trying to do is I have a Command Button to create a new record. For that i use a macro with the 'GoToRecord' Action. When I click the button all the fields clear including the comboxes. I use the above code to intercept the not in list error and allow the user to procede with adding a new value in both of the combo boxes. But when I tab or hit return after putting in a new value for both comb boxes the values are removed. Now I did test the other text fields to see if they are saving values and they are so that is good.

Thanks,

-Dev1

mshmyob:

Any thoughts on what I provided?

-Dev1

[Dev1]

mshmyob:

Any thoughts on what I provided?

-Dev1

Can anyone help base on my second to the last post?

[mshmyob]

As I said erlier

Because the combo box is not bound it will not save in your table. The code you have below is used to TEMPORARILY save your ROWSOURCE.

When you open the form again the changes will be gone.

The procedure updates the list only for the current work session. You can’t permanently update the value list in Form view. The only way to accomplish that is to open the form in Design view, update the value list, and then save the form.

To get that to work properly you must set the LIMIT TO LIST = YES
You have it set to NO. Your value will then be saved to your table BUT when you close the form and open it again that value will NOT appear in your combo box as a choice. It will be in your table but not the combo box as a choice for future use.

[Dev1]

As I said erlier

Because the combo box is not bound it will not save in your table. The code you have below is used to TEMPORARILY save your ROWSOURCE.

When you open the form again the changes will be gone.

The procedure updates the list only for the current work session. You can’t permanently update the value list in Form view. The only way to accomplish that is to open the form in Design view, update the value list, and then save the form.

To get that to work properly you must set the LIMIT TO LIST = YES
You have it set to NO. Your value will then be saved to your table BUT when you close the form and open it again that value will NOT appear in your combo box as a choice. It will be in your table but not the combo box as a choice for future use.

Well obviously your first option is not a valid solution. That isn't even a practicle solution...

Thanks you for you help, but i really need a solution not critizim!

-Dev1

[mshmyob]

Nobody is criticizing. I am pointing out that to make your values save to your table you need to set the LIMIT TO LIST property = YES. Otherwise you NOT IN LIST event will never run.

But my point that the Row Source will keep clearing the next time you open the form still holds true.

I would try:

1. Make your control box bound to the field in your table
2. Set your Control Source to something like the following
SELECT DISTINCT Table1.tcboBox FROM Table1;
3. LIMIT TO LIST property = NO
4. ALLOW VALUE LIST EDITS property to YES

This will allow you to add values to your table which will appear in your combo box and if it not a primary key the DISTINCT word will eliminate duplicates from appearing in the combo box.

Well obviously your first option is not a valid solution. That isn't even a practicle solution...

Thanks you for you help, but i really need a solution not critizim!

-Dev1