Create New Record in Form

[Dev1]

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!Opportu nity!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

[mshmyob]

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!Opportu nity!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

[Dev1]

My Sample Form

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

[mshmyob]

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

[Dev1]

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

[mshmyob]

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.

[Dev1]

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

[mshmyob]

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

[Dev1]

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

[Dev1]

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.Requ ery
End Sub ?

Which combo box is that?

Thank you

-Dev1

[Dev1]

Question, what is:

Private Sub Combo94_Click()
Me.Combo81.Requ ery
End Sub ?

Which combo box is that?

Thank you

-Dev1

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

-Dev1

[mshmyob]

That was just a test I did. You can delete it if you want.

Question, what is:

Private Sub Combo94_Click()
Me.Combo81.Requ ery
End Sub ?

Which combo box is that?

Thank you

-Dev1

[mshmyob]

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

[Dev1]

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

[mshmyob]

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