Update of unbound text boxes and leading zeroes in a form (2 Viewers)

[AnilBagga]

I have 3 issues with the form enclosed, 2 of them are related to the updation of unbound text boxes. Would appreciate some help.

1)
I have several unbound text boxes in a form. The values are updated in these text boxes using the column function of cbo boxes 'Country' and 'ConsigneeCode'. However when the form is reopened, the text boxes are blank.

If I refresh the cbo value, the text boxes are updated.

I tried requery of these fields in form load event, it did not work. How can it be done. The attached DB has only one form in question.

If it cannot be triggered by opening of the form, is there some command button which can be added to update all records?

2)
I have added a Dateadd function to get Exptd Delivery Date. There is an unbound text box I am using for this - Transit. This unbound text box is again updated with cbo Consignee code field. However this resultant value ExptdDelyDate is updated when form is reopened, unlike the above text boxes. However it is not refreshed unless form is closed and reopened!

Me.ExptdDelyDate = DateAdd("d", [Transit], [ExptdSailingDate])

3)
The Booking Ref uses a counter which is concatenated using a counter (unbound text boxes are hidden). Can we get a dynamic set of leading zeroes in the reference number e.g 0001, 0012, 0987, 9821 etc

 

[arnelgp]

the form is asking for some xlsx file.
anyway why not use a Query instead of table as the Form's Record Source.
you can specify those "unbound" columns in your query.

set the textbox's Lock property to Yes so it will not be edited.

 

[Gasman]

DB is later than I can edit.

3. Use format property of the control.
You have a linked Excel workbook?

 

[AnilBagga]

the form is asking for some xlsx file.
anyway why not use a Query instead of table as the Form's Record Source.
you can specify those "unbound" columns in your query.

set the textbox's Lock property to Yes so it will not be edited.

Sorry. The excel file was linked to my computer. Added as a table now

Adding unbound textboxes through the Query is simple now. I created a from and a Query called Test for this purpose

Question
1.. How do I save the expression of Query to the data in the table. I have created an expression of Transit (it is date field) in the Query and added that (for ref only) in the new form linked to the Query. This value needs to be save to the table field ExptdDelyDate. I added an event for this target field but it is not updating!

2. I changed the format of the counter UniqueNr for leading zeroes. I hope this is right way. However when I create an expression in the quey for booking reference, the leading zeroes are eliminated. I presume the number needs to be converted to text before concatenating. How?

3. To build the UniqueNr, I need to build a counter in the Query. Basically it has to start from 0001 and go on till the end of 2020 and restart from 0001 on 1st Jan 2021. I dont want to use the ID of the field because if someone deletes the record due to an error, the reference no series will also have a missing number. I guess an expression Dmax to read the highest value of theis field and then incremented by 1 should be the way. How can it be done?

4. I cannot add new records after using query as a record source!!

 

[bastanu]

Please have a look at the updated file. I have changed the tables (made RoutingID a primary key in ShippingMaster, added index to CustCode). Also changed the recordset type to Dynaset inconsistent updates for both the query and the form and it is now editable.. Moved the code to the current event of the form (to transfer the transit to expected delivery).
Cheers,

 

[AnilBagga]

Please have a look at the updated file. I have changed the tables (made RoutingID a primary key in ShippingMaster, added index to CustCode). Also changed the recordset type to Dynaset inconsistent updates for both the query and the form and it is now editable.. Moved the code to the current event of the form (to transfer the transit to expected delivery).
Cheers,

Thanks Vlad. It is always a pleasure to read your responses

Will 'digest' this tomorrow. I am sure I will have questions

 

[AnilBagga]

Please have a look at the updated file. I have changed the tables (made RoutingID a primary key in ShippingMaster, added index to CustCode). Also changed the recordset type to Dynaset inconsistent updates for both the query and the form and it is now editable.. Moved the code to the current event of the form (to transfer the transit to expected delivery).
Cheers,

Dear Vlad

1. PK on ShippingMaster was an avoidable mistake

2.. Indexing CustCode is that the cause of the problem? Should I do this in my main database too? The customer master in this DB is a copy of the mainDB

3. Dynaset inconsistent - in what situation does one choose this? Was this one of the reasons for the problem?

4. Ideally I will like to reset the counter UniqueNr to 0001 in 2021. So every year the ref no on 1st Jan should read as CB/2021/0001 or CB/2022/0001. How can this be done - some kind of IIF condition as below. I am not sure how to construct this correctly because technically the function in red fonts below should be reading the UniqueNr of the previous record, and not Dmax

IIF("Year(date()-year(Dmax("Requestdate","tblContainerBooking")"=1, UniqueNr=0001,(Nz(DMax("UniqueNr", "tblContainerBooking"), 0) + 1))

5. I put the qry as record source of my earlier frmContainerbooking which was already formatted and I changed the bound conditions of concerned fields and added the Event codes as done in the frmTest by you. I am facing the following issues

a) Unbound controls are not refreshing after I change the consigneecode. They were not changing in frmTest too. I added me.refresh in the event, not working. Refreshall in the ribbon does the trick but the control goes to record no 1. How can we maintain the focus on current record and also refresh without using Refreshall button

b) I had to include UniqueNr in the form to be able to use it in the Afterupdate event. Can we reference the field in the table directly in Afterupdate Event w/o including the field in the form?. Of course I can make it invisible in the form, but if I can reference to the table directly , it is better. I tried tbl.customerbooking.UniqueNr for referncing - does not work.

6. I was working on the form again and realised that it has another issue. The UniqueNr and thereby the Booking Ref which uses this number, should not be changed once the record is saved. Currently it keeps changing if we edit that record or other records!

 

[bastanu]

Hi,
Here are some answers:
2. Any field used in joins should be indexed.
3. See https://support.microsoft.com/en-us...property-dbe9dd7a-cc73-4539-aaa7-9e6b55034052. I don't use that a lot as it can cause some data integrity issues but it does allow you to edit fields in one-to-many type relationships like you have.
4. You need to use a totals query to group by RequestYear :Year([RequestDate]) and get the Max for that year
5a. I think this is caused by the recordset type (dynaset inconsistent updates), you can store the current ID, do a me.requery then return the the record (see attached)
5b. You can have it hidden or use dlookup ("UniqueNr),"tblYourTable","[ID]+" & [ID]) to get it.
6. I don't get how they change, I thought they are stored in the table.

Cheers,
Vlad