Newbie Combo Box Conundrum

[newtoaccess7]

Hi folks,
I have a table containing photographers names and addresses. Of course, each photographer record has its own ID#. I've also created a form to list each photo shoot a photographer is hired for. In that form, the first field is for the photographer's name. I have a combo box that displays the photographers last name and first name, each pulled from the photographers table. The row source info looks like the following for this field in the form:

SELECT [LAST NAME] & ", " & [FIRST NAME] AS Photographers, [LAST NAME] & "," & [FIRST NAME] AS Expr1, [Photographers].[ID] FROM Photographers ORDER BY [LAST NAME] & "," & [FIRST NAME];

I have two problems. 1. Every time I went to save a photo shoot, I would get the error message, "The field 'Photo Shoots Photographer ID' cannot contain a null value because the Required property for this field is set to True." So, I changed this property in the photographers table, and I was then able to save a photo shoot record. However, this led to problem #2. While each new photo shoot record is being saved to the photo shoots table, no photographer name is being assigned each shoot.

What am I doing wrong? I know this is probably something simple.


2. However

 

[Remou]

You need a standard main form / subform set-up. The tables would be, say:

[tt]Photographers (one)
PhotogID -> Primary key
Etc.

PhotoShoots (many)
PhotoShootID -> Primary key
PhotogID -> Foreign key[/tt]

As you can see, the link to the PhotoShoots table is the photographer ID, not name. When these two tables are set up as a form and subform using the wizards, the Link Child and Link Master Fields will be set to PhotogID, which will be automatically written to the PhotoShoots table.