CODEWITHSUNDEEP
ms-accessvbams-access-2010
sab0tage
sab0tage

Reputation: 53

MS Access - Form tries to create new record on open and record jumps about a continuous form

So I have two issues with my database, which I think are linked together.

Firstly if tblDeliveries is empty and frmMainView is opened it comes up with "You must enter a value in the 'tblDeliveries.deliverySlot' field" but the form shouldn't be trying to create a record and I can't see any reason why it would. Opening frmDailyView (which is a subform of frmMainView) doesn't show this behaviour but it is the one with the continuous form on...

Secondly if tblDeliveries has one (or more) rows the first row of the table will jump about... For example in frmMainView if the date of the first record is set to 23/01/2015 and you navigate to the 24/01/2015 and click anywhere on the subform and then press F5 to refresh the record will have jumped to that date, the date even changes in tblDeliveries.

I have no idea why it's doing it, I even removed all of the VBA code for the form to stop it, but it happens anyway.

If anyone has come across anything like this before, please let me know how you fixed it, or any suggestions would be very welcome. Thanks!

EDIT: If you download the database, add your computer login name to tblUsers. Also the date is UK formatted, so might need tweaking to work with US dates.

The offending database (1.5MB)

Upvotes: 0

Views: 412

Answers (3)

sab0tage
sab0tage

Reputation: 53

I've resolved the issue, thanks to some of the comments I realised I could just give the deliverySlot field a default value with a number outside of the range it would look for, I set it to 99, but it only looks for 1-20. The date still jumps around but since it's not in the range of the query it's hidden. It's resolved both issues.

Upvotes: 0

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112752

I am not re-iterating the points of @Parfait.

Access Forms have a property DataEntry. If it is set to Yes (or True in VBA), then the form will open itself pointing to a new empty data record. Set this property to No (False in VBA).

If you want to give the user the possibility to manually enter a new record, set the property AllowAdditions to Yes and to No otherwise. Yes displays an empty record marked with a star * and the end of the data sheet or continuous form.

Upvotes: 0

Parfait
Parfait

Reputation: 107767

Taking a look at your database, several issues are occurring.

You first issue can be resolved by going into the Design View of the table tblDeliveries, select the deliverySlot field and change Required (in bottom panel) from Yes to No.

Your second issue is multi-faceted with many issues but a critical problem is that the parent-child link field, deliveryDate, between the main form and subform is missing in the recordsource of the sub form and the field's table origin is not in the query's join statements. For me, I can't even update anything in subform (all boxes are grayed out).

Essentially, you are trying to normalize Delivery Dates and Delivery Times (one-to-many) but the left join query (qryDeliveryIncBlanks) setup is not aligned to this effect. You user login also does not integrate smoothly into frmMainView. My ultimate advice is revamp your database design relational model then use forms as the visual representation of those related tables.

Upvotes: 0

Related Questions