Does anyone know why?

[Kiwi-Wombat]

Attached is a simple database. It is purely a mock-up to demonstrate my question.

There are two forms that are identical except for their source. In Form1 the Record Source is a table. In Form2 the Source is an SQL statement. I am asuming that I have set everything up correctly but, knowing me, I may not have.

In the Produce table both the Quantity and InSeason fields have default values.

In Form1 when you go to a new record, the default values of 0 in the Qantity field and True in the InSeason fields are correctly shown.

But in Form2 when you go to a new record, Quantity is empty and InSeason is greyed out.

Can someone tell me why and how to overcome this


A second question and probably much simpler.

If you Tab from field to field, after the last field it goes to a new record. How do you make it stay in the same record?

Thanks

 

[dt01pqt]

A second question and probably much simpler.

If you Tab from field to field, after the last field it goes to a new record. How do you make it stay in the same record?

Thanks

In form's properties set cycle to current record/page.

 

[dt01pqt]

But in Form2 when you go to a new record, Quantity is empty and InSeason is greyed out.

Can someone tell me why and how to overcome this

When you use a table there is a placeholder for new record which hold's the default value of each field. When you make a query it draws up it collects the records and orders them (regardless if you specify) so it doesn't have a placeholder. So default values will not be passed form's datasheet's placeholder. As soon as you enter valid data it appends the tables.

My first piece of advice is to separate data entry and data view interfaces. This is a good idea because it ensures the user acknowledges what he/she is doing. Therefore you will find you get considerably less erroneous data. You can manage this all on the one form by it is simpler to have two. Also make editing a record into a process so you don't get accidental edits.

What has this got to do with your question? Well in data view default values aren't usually relevant and in data entry default values are better assigned the control on the form rather than the record field in the table and this will solve your problem. Data entry is great because it is only for new records there is no confusion. If you want to know more about the specifics let me know.

 

[Kiwi-Wombat]

Thanks for your response.

Cycle works great.

The other issue is a curious one. In my real life form I do use the same page but change the special effect for viewing i.e. flat and allow edits NO and sunken for data entry and switch between them.

The reason I asked the question has to do with a previous post of mine and I thought the issues were related.

You will notice that the produce database copies the fruit name from the fruit table and adds it to the produce table. This adds to the size of the table because the information is duplicated in two tables. I did it this way because that was what the answer to my post said to do, but ideally one should only have the ID number of the fruit in the produce table (I assume that is the ideal, I really am still a newbie).

See new attachment.

Assume for a moment that the name of the fruit was optional in the produce table. Obvioulsy in the example given it would be non-sensical but lets assume you have a field that is a look-up but it is optional to complete.

Now when you save (without selecting a fruit) you get an error. See Form3.

 

[daveUK]

The FruitID is a FK in TblProduce and a PK in TblFruit, this field must be completed as you cannot save a record with the PK as a null value.

In order to stop users from trying to save without selecting a fruit use the following code

If Me.FruitID = False Then
    MsgBox "You MUST select a Fruit from the list"
    Me.FruitID.SetFocus
Else
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If

 

[Kiwi-Wombat]

Thanks

In the real programme, the user must have the option of leaving the particular field empty. The lookup table is only needed to assist in data entry and to ensure correct spelling of some unusual words. It is interesting because I thought someone must have come across it before.

The obvious workaround is to have the first record in the fruit table as a blank and in the produce table set the default value of Fruit ID to 1.

It's dirty but overcomes my problem

Thanks for your collective assistance