By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Create New Record in Form

P: 38
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
Feb 14 '08 #1
Share this Question
Share on Google+
33 Replies


mshmyob
Expert 100+
P: 903
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
Feb 14 '08 #2

P: 38
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
Feb 14 '08 #3

P: 38
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
Feb 14 '08 #4

mshmyob
Expert 100+
P: 903
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?
Feb 14 '08 #5

P: 38
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:

Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  2.  
  3.     ' Return Control object that points to combo box.
  4.     Set ctl = Me!Combo83
  5.     ' Prompt user to verify they wish to add new value.
  6.     If MsgBox("Value is not in list. Add it?", _
  7.          vbOKCancel) = vbOK Then
  8.         ' Set Response argument to indicate that data
  9.         ' is being added.
  10.         Response = acDataErrAdded
  11.         ' Add string in NewData argument to row source.
  12.         ctl.RowSource = ctl.RowSource & ";" & NewData
  13.     Else
  14.     ' If user chooses Cancel, suppress error message
  15.     ' and undo changes.
  16.         Response = acDataErrContinue
  17.         ctl.Undo
  18.     End If
  19.  
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
Feb 14 '08 #6

P: 38
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 = acDataErrContinue
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
Feb 14 '08 #7

mshmyob
Expert 100+
P: 903
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:

Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  2.  
  3.     ' Return Control object that points to combo box.
  4.     Set ctl = Me!Combo83
  5.     ' Prompt user to verify they wish to add new value.
  6.     If MsgBox("Value is not in list. Add it?", _
  7.          vbOKCancel) = vbOK Then
  8.         ' Set Response argument to indicate that data
  9.         ' is being added.
  10.         Response = acDataErrAdded
  11.         ' Add string in NewData argument to row source.
  12.         ctl.RowSource = ctl.RowSource & ";" & NewData
  13.     Else
  14.     ' If user chooses Cancel, suppress error message
  15.     ' and undo changes.
  16.         Response = acDataErrContinue
  17.         ctl.Undo
  18.     End If
  19.  
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
Feb 14 '08 #8

mshmyob
Expert 100+
P: 903
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
Feb 14 '08 #9

P: 38
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 = acDataErrContinue
ctl.Undo
End If


Combo Box2

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

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

Event: AfterUpdate()
Private Sub Combo81_AfterUpdate()
If IsNull(Me.Combo81) 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 = acDataErrContinue
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
Feb 14 '08 #10

P: 38
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 = acDataErrContinue
ctl.Undo
End If


Combo Box2

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

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

Event: AfterUpdate()
Private Sub Combo81_AfterUpdate()
If IsNull(Me.Combo81) 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 = acDataErrContinue
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
Feb 14 '08 #11

P: 38
mshmyob:

Any thoughts on what I provided?

-Dev1

Can anyone help base on my second to the last post?
Feb 14 '08 #12

mshmyob
Expert 100+
P: 903
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.
Feb 14 '08 #13

P: 38
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
Feb 14 '08 #14

mshmyob
Expert 100+
P: 903
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
Feb 14 '08 #15

P: 38
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.
Thank you for the response! I'm becoming very fustrated and it's starting to show:).

Well If I understand you correctly the first combo box I have does the following:

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

The second combo box which is dependend on the first is:
Table/Query
SELECT DISTINCT [Sales Funnel1].Company FROM [Sales Funnel1] WHERE ((([Sales Funnel1].[ASC Rep Reporting])=Forms!Opportunity!Combo83)) ORDER BY [Sales Funnel1].Company;

"Comb83 bieng the above combobox"

Bound Column:1
Limit to List: Yes
Auto Expand: Yes.


As of right now the I can't save to either control like you indicated previously. If you could be a little more grainular that would be helpful. When you mention the steps above they are not detailed enough for my lack of knowing the Access Idea, which I do apoligize for!!!

-Dev1
Feb 14 '08 #16

mshmyob
Expert 100+
P: 903
I have made an attachment of a sample.

1. Two combo boxes
2. When you make a selection on the first the second gets populated based on the first combo box
3. All values get saved to the table
4. All new values are available for future use.


See if this helps any.

Thank you for the response! I'm becoming very fustrated and it's starting to show:).

Well If I understand you correctly the first combo box I have does the following:

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

The second combo box which is dependend on the first is:
Table/Query
SELECT DISTINCT [Sales Funnel1].Company FROM [Sales Funnel1] WHERE ((([Sales Funnel1].[ASC Rep Reporting])=Forms!Opportunity!Combo83)) ORDER BY [Sales Funnel1].Company;

"Comb83 bieng the above combobox"

Bound Column:1
Limit to List: Yes
Auto Expand: Yes.


As of right now the I can't save to either control like you indicated previously. If you could be a little more grainular that would be helpful. When you mention the steps above they are not detailed enough for my lack of knowing the Access Idea, which I do apoligize for!!!

-Dev1
Attached Files
File Type: zip combobox.zip (17.5 KB, 56 views)
Feb 15 '08 #17

P: 38
I have made an attachment of a sample.

1. Two combo boxes
2. When you make a selection on the first the second gets populated based on the first combo box
3. All values get saved to the table
4. All new values are available for future use.


See if this helps any.
How do you add attachments on this site? I don't see how you do it.

Thanks,

-Dev1
Attached Files
File Type: zip test.zip (621.6 KB, 50 views)
Feb 15 '08 #18

mshmyob
Expert 100+
P: 903
Yeah it took me awhile to figure it out also.

At the bottom of your post it says Edit/Delete. Select that and then you have a place to add an attachment.

But suprisingly you cannot delete your post (lol)

How do you add attachments on this site? I don't see how you do it.

Thanks,

-Dev1
Feb 15 '08 #19

P: 38
Yeah it took me awhile to figure it out also.

At the bottom of your post it says Edit/Delete. Select that and then you have a place to add an attachment.

But suprisingly you cannot delete your post (lol)

I added my MDB in the post just before this one, as an attachment. You will see the form. What you sent me as a example isnt quite what i was looking for. I have the dependent functinality working among the two combo boxes. When you open the mdb open the 'Opportunity' form and you will see ASC Rep Reporting and Company. Try to add a new value for each and watch what happens. I'm thinking I will have to make a new form just to add a new record to the table.

-Dev1
Feb 15 '08 #20

mshmyob
Expert 100+
P: 903
I have made a slight change to your file that allows your 2 fields to save.

The biggest problem you are having is that the design is not correct. You have 3 tables that are not related to each other and your main table is just 1 big table with massive amounts of redundancy.

You may want to consider doing a whole redesign and you won't have so much problems. Look at the following link
http://www.thescripts.com/forum/thread585228.html

Hope this helps.
Attached Files
File Type: zip test.zip (289.5 KB, 65 views)
Feb 15 '08 #21

P: 38
I have made a slight change to your file that allows your 2 fields to save.

The biggest problem you are having is that the design is not correct. You have 3 tables that are not related to each other and your main table is just 1 big table with massive amounts of redundancy.

You may want to consider doing a whole redesign and you won't have so much problems. Look at the following link
http://www.thescripts.com/forum/thread585228.html

Hope this helps.
mshmyob:

Yes I did get the file. Sorry for the late response, but I had to fufill my valentine obligations! I've just opened the file and looking to see where you made the changes.

Yes, yes you are correct about the design, horrible!! I know but when this assignment was given to me which was about 4 days ago, I've never touched Access before. Not make an excuse but the person who this was assgined to really didn't know how to build any solution and when I asked about the who what when and where they just needed something really quick. Which lead to the bad desgin. I wanted to go that route obvisouly but time would not permit.. not mine theres!!! I'm still digging into it. One thing I have noticed is that when you do add a new record you have to close the form in order for the new addition of the record to take effect. I'm looking to see if I can just add a save button. But it looks like that save functionality when add a value or record is done automaticly.

Thank you!!!!


-Dev1
Feb 15 '08 #22

mshmyob
Expert 100+
P: 903
No problem. I did redo it by the way to have you add a record because I noticed the problem with that when I was checking it for you. But I took out that code so you could try it on your own. (lol)

mshmyob:

Yes I did get the file. Sorry for the late response, but I had to fufill my valentine obligations! I've just opened the file and looking to see where you made the changes.

Yes, yes you are correct about the design, horrible!! I know but when this assignment was given to me which was about 4 days ago, I've never touched Access before. Not make an excuse but the person who this was assgined to really didn't know how to build any solution and when I asked about the who what when and where they just needed something really quick. Which lead to the bad desgin. I wanted to go that route obvisouly but time would not permit.. not mine theres!!! I'm still digging into it. One thing I have noticed is that when you do add a new record you have to close the form in order for the new addition of the record to take effect. I'm looking to see if I can just add a save button. But it looks like that save functionality when add a value or record is done automaticly.

Thank you!!!!


-Dev1
Feb 15 '08 #23

P: 38
No problem. I did redo it by the way to have you add a record because I noticed the problem with that when I was checking it for you. But I took out that code so you could try it on your own. (lol)

Ok, fare enough one question then. Can I code the save functionality without creating a new button object? Meaning put the code on/in the form itself?

Thanks!

-Dev1
Feb 15 '08 #24

P: 38
Ok, fare enough one question then. Can I code the save functionality without creating a new button object? Meaning put the code on/in the form itself?

Thanks!

-Dev1

Question, what is:

Private Sub Combo94_Click()
Me.Combo81.Requery
End Sub ?

Which combo box is that?

Thank you

-Dev1
Feb 15 '08 #25

P: 38
Question, what is:

Private Sub Combo94_Click()
Me.Combo81.Requery
End Sub ?

Which combo box is that?

Thank you

-Dev1
What ever you did is causing the filtering not to work!!

-Dev1
Feb 15 '08 #26

mshmyob
Expert 100+
P: 903
That was just a test I did. You can delete it if you want.
Question, what is:

Private Sub Combo94_Click()
Me.Combo81.Requery
End Sub ?

Which combo box is that?

Thank you

-Dev1
Feb 15 '08 #27

mshmyob
Expert 100+
P: 903
I will try to rewrite a bit of the code to help you out.

The problem is that with the underling table structure being so wrong what you are trying to do is like forcing a round peg in a square hole. It would probably be so much quicker to just start from scratch and do it correctly.

But I will try to work with what you have. Stay tuned.

What ever you did is causing the filtering not to work!!

-Dev1
Feb 15 '08 #28

P: 38
I will try to rewrite a bit of the code to help you out.

The problem is that with the underling table structure being so wrong what you are trying to do is like forcing a round peg in a square hole. It would probably be so much quicker to just start from scratch and do it correctly.

But I will try to work with what you have. Stay tuned.

Thank you, that makes since. I'm going to attempt a new data schema and post it by early tomorrow morning. Again I do appreciate your help. Just a curious quesion. If done right how long something like this would normaly take from start to finish?

-Dev1
Feb 15 '08 #29

mshmyob
Expert 100+
P: 903
For your scenario - It might take at most 1/2 hour to do the schema.

The forms for data entry maybe an 1 or 2 hours just to make them nice.


Throw in a couple of reports and a menu you could do everything in a few hours at most.


Thank you, that makes since. I'm going to attempt a new data schema and post it by early tomorrow morning. Again I do appreciate your help. Just a curious quesion. If done right how long something like this would normaly take from start to finish?

-Dev1
Feb 15 '08 #30

P: 38
For your scenario - It might take at most 1/2 hour to do the schema.

The forms for data entry maybe an 1 or 2 hours just to make them nice.


Throw in a couple of reports and a menu you could do everything in a few hours at most.

One other question. Can I have a sql server backend and a Access Forms front end (UI)?

-Dev1
Feb 16 '08 #31

mshmyob
Expert 100+
P: 903
Yes.....................................

One other question. Can I have a sql server backend and a Access Forms front end (UI)?

-Dev1
Feb 16 '08 #32

P: 38
Yes.....................................
One other question. If you have a relation data schema, how do you facilitate a import of data from one excel spread sheet to populate multiple tables?

Thanks you in advance,

-Dev1
Feb 18 '08 #33

mshmyob
Expert 100+
P: 903
Create a new thread for this question.

One other question. If you have a relation data schema, how do you facilitate a import of data from one excel spread sheet to populate multiple tables?

Thanks you in advance,

-Dev1
Feb 20 '08 #34

Post your reply

Sign in to post your reply or Sign up for a free account.