Add record using combo box

[tMitch]

Hi -

I would like to add a new record, selected from tblPlantlist, to tblProjectPlantList using a combo box (linked to tblPlantList) in a form. Once the new record is added, I then want to add additional information relevant to the Project in the form. It seems simple, but it's sure not working.

Can anyone help me out or direct me to specific links that address this? I've searched for info, but am not finding anything that helps.

Thanks.

 

[llkhoutx]

If tblPlantlist and tblProjectPlantList have a common field. Use tblPlantlist in your combo box, with the common field being the bound field in the combo box, but unbound to tblProjectPlantList, then have your form bound to tblProjectPlantList and filtered by tblPlantlist.

 

[tMitch]

Thanks for replying. I made the combo box a filter for the form using:

Me.RecordSource = "SELECT * FROM tblPlantList WHERE Plant_ID =" & Me!cboPlant
Me.Requery

The Plant_ID from tblProjectPlantList is now filtering and showing up correctly on the form, but the other fields read "#Name?"

Could the problem be that it's a one-to-many relationship (tblProjectPlantList have many Plant_IDs)? Is this not going to work?

 

[llkhoutx]

THe fields with "#Name" are not bound correctly to the form recordsource, that is, their Control Source (a field property on your form) is not set to a field in the recordsource.

 

[tMitch]

Okay, it's working now - the combo box brings up the correct records (I had "Select from tblPlantList" instead of tblProjectPlantList).

But I want to be able to add a record. When plant is selected in cboPlant and is not already on tblProjectPlantList, I want to add the plant and add additional info.

But it won't let me do this. I get a message say "Index or Primary cannot be null." Debug sends me to my Me.recordsource code (see previous reply). Since an ID number doesn't exist for record not yet added, it won't let me add new record. ???

Is there some code I can use to be able to filter and to add new record?

Thanks.

 

[llkhoutx]

Use the NotInList event of the combobox to add a Plant to tblPlantList, then a new (blank) record should display.

If you need the code for NotInList, I think that I've posted it on this site before. It's in most Access books.

 

[tMitch]

Sorry, I’m still struggling with this. I don’t know code, so it’s difficult. I find info on adding to cboBox, but not to also add to the form.

I need code to allow tblProjPlantList (table form is bound to) to create new auto ID number when I select a plant from the cboBox that is not already in the tblProjPlantList table. Also PlantID doesn't seem to show up in form either, so I get msg that "Index or Primary Key cannot contain null value"

I used your code in this thread:

http://www.access-programmers.co.uk/forums/showthread.php?threadid=21581&highlight=NotinList+event

I tried to adapt it to my database. At this point it’s saying Sub or Function not defined because at GetNextNameID. I’m not sure what this is referring to. You did answer that question in another posting, but I didn’t understand the answer (how do I increment it?).

Am totally off track here?