New data entry from combo box

[RuralGuy]

Glad I could help.

 

[DarkProdigy]

hey RG, it's me again

I tried to implement what you did in that sample db into mine. I basically copied and pasted the code but it doesn't seem to want to work for me.

When I go to select an item from the list box, I get "Run-time error '7591', improper reference to the RecordsetClone property" and when I go to input a new entry, I get "Compile Error: Variable not defined" and it highlights the dbFailOnError under the NotInList Event

 

[RuralGuy]

You need a reference to Microsoft DAO 3.x Object Library. <ALT> F11 and Tools>References... and scroll down and select it. Then try compile again.

 

[DarkProdigy]

Ok, that helped for the new input

but for the list selection, it's still giving me the 7591 Runt time error

 

[RuralGuy]

Uncheck the reference to ActiveX Data Objects 2.x Library and compile again. If you get different errors then go back and check it again. It will be lower in the list and should still resolve these problems.

 

[DarkProdigy]

ya, I get the same error message each time and it highlights this part in the code

Me.RecordsetClone.FindFirst "[ID] = " & Me.cboOptions.Column(0)

 

[RuralGuy]

Are you using a bound form? If not the this piece of code will not work and it will throw that error because there is no RecordSet.

 

[DarkProdigy]

I don't know what you mean by "bound"

I just started adding stuff to it in design view from a blank form

 

[RuralGuy]

Take another look at my example. The form needs a RecordSource for this method to work.

 

[DarkProdigy]

ok, I get that

but how would I do it when I have multiple combo boxes filtering multiple queries?

 

[RuralGuy]

Maybe this link will help: Cascading Lists for Access Forms

 

[DarkProdigy]

Ok, I figured a work around for it. What I did was created a unique subform for the options data which I pretty much just copied from your sample RG

Thanks again for all of your help and patience.

 

[RuralGuy]

No problem. Glad to help.

 

[Banana]

I have a question regarding not in list combobox... It's bound to a query looking up person's names.

However the tblPerson has four fields that is related and has RI enforced. I imagine that if I tried to add a new name to the list, the recordset would silently fail because there will be no related records.

How would I program the NotInList event to "hold" the name until the user has entered in all related data before updating the recordset with new name along with the related data?

 

[RuralGuy]

Use the NotInList event to open a second form in Dialog mode where you have the user complete all of the required information. The table will be updated prior to the code returning to your NotInList event and setting Response = acDataErrAdded will cause the ComboBox to Requery where it will find the "new" data.

 

[Banana]

But the related data are already on the same form; hence the question. Or is it really better moving them to a dialog form?

 

[RuralGuy]

I guess I'm having some difficulty understanding how you are using the ComboBox. Can you post a small db that demonstrates the issue? Or maybe just describe the issue in different terms so my old brain can get a handle on it.

 

[Banana]

To keep things relevant, we're talking about a form with two related subforms.

Typically, a user would type in a person's name in the first combobox and when selected, the related four combobox will autofill for user's review, then the user fills in subform as necessary.

Now, I want to allow user to enter a new name, but since a new record does not have the data that is related and required, it will cause an error before the user can enter the related data in the three combobox to right of the attendee name.

For now, I've thought of putting in bogus data to satisfy the requirement until response=acDataErrAdded then have all combobox set to null, to force user to make their selections.... But I'm thinking it's not the best solution, though.

Here is a dump.

Oh, I almost forgot- the combobox in question is unbound as the query to concentate the name can't be updated.... (though my other combobox on other form which acts almost exactly same is bound to a query which is exactly identical to the concentating query is updatable.... )

 

[RuralGuy]

Are you using the [cboAttendeeName] combobox to look up a record in the query/table to which the form is bound? If [cboGender] and [cboCategory] are bound to that same query/table and both SubForms LinkChild/MasterFields are pointing to an AutoNumber field PrimaryKey, than I don't see what problem you will have. With RI turned on, it is ok to have a parent record without any children. It just can not be the other way around as in real life. Am I missing something?

 

[Banana]

Really?

You are correct; those fields are bound to the same table that cboAttendeeName looks up for name.

However, If I try to do just that, I get a error saying that a related record is required in those fields. And yes, those records are a "many side"

If I turn off the RI, no problem.

But I want RI to be on, so I assumed that it was how Access works.....