Combobox problem with new records (1 Viewer)

Hello everybody,

Another question from a newbie working on a database that is way too comlicated for him (but I'm learning alot )

My problem is with a form that is based on a query1 that mainly joins images (filenames) from a table to records in another query2. The Join includes ALL records from the image table and only those records from query2 where the join fields are equal. Here this means the images can have a join with a record or have a null value.

My form has a bound combobox that has query2 as its row source. Now, when I create a new record using the forms navigation buttons and I populate any of the other boxes that are bound to fields in query1, I get an error message because the combobox has tried to automatically create a new record in query2 (this goes wrong because there are null values in certain fields but that's not the problem).

But I don't want a new record in query1 to cause a new record in query2! I want the join to query2 to have a null value untill I populate it with an existing value.

What puzzles me is that when I work directly in query1, this problem does not arise. So I guess the error is in the form and/or its combobox properties.

If anyone has an idea what is going wrong here, I would be much obliged. I have tried to detail my problem as good as possible but I'm very new to all the right terminology so if anything is unclear, please ask.

Many thanks in advance!

Your first problem is one of semantics.
You do not create a new record in a query, the new record is created in a table. You can instruct the query to create a new record, but that record is not saved in the query, it's saved in a table.

From your description, you may be trying to copy fields from Query2 into your table, but those fields don't exist in the table. That would explain why you're getting an error.
Also, from the sound of it your Query1 is redundant.

If it is your intention to copy data from your picture table, I would only use the combo box which contains that data. You will have to do some coding to copy the fields from the picture table into your table when you make a selection on the combo box.

Those fields which do not contain any data will remain blank, although as you have the partial record you wish on the form now, why not enter all the other data now.

Hi Statsman,

Many thanks for reading my post and for helping me.

First, my apologies for the symantics problem: it's not easy for a beginner.... But I do understand the very basic mechanisms of tables and queries you explained (records are not created and saved in queries but in tables).

Let me try again to explain better what I am trying to do with my form frmImages. I have many tables but this is mainly about tblImages and tblSpecimens. tblImages has fields with information regarding images and a specimenID field that joins it to tblSpecimens. tblSpecimens has many fields that are joined to other tables.

I want to use my frmImages to populate fields in tblImages AND, if the specimenID is not null, to populate fields in tblSpecimens also. Therefore, I have created query1, with fields from both tables.

(I do not want to copy data, nor do I want to necessarily populate all fields in frmImages)

To make the combobox for the tblImages specimenID field informative (just the number doesn't say much) I have created query2 to serve as a row source.

When I create a new record using frmImages, I want it to create a record in tblImages only. But right now it also creates a new record in tblSpecimens. This is my problem.

I hope this new explanation makes more sense (though I am sure the symantics are still wrong so please let me know if anything is still unclear). And that you have an idea what may cause this problem.

One thing I can think of is that query1 contains two autonumbered fields: imageID and specimenID so maybe that causes my problem?

Any ideas or suggestions would be very much appreciated!

Thanks again