Table updates as form inputs into another table.

[nico5038]

I've started with changing your table structure. (Check the database relations!)
A tblAgent has been added to record the UpgradeQuantity .

The table tblBalance has an additional date and the NormalBalance has been dropped as "Starting balance as of 1 January" can be recorded on that date as a DateUpgrade qty while dropping the older dates.

Opening the form triggers the warning and a Yes will add the rows with the upgrade qty.

Finally the Balance on the form is made visible after selecting the Agent.

To finalize this you would need an additional FinalBlance being the Dsum() of the tblBalance minus the Dsum() of the tblLeave qty.

Nic;o)

[MMcCarthy]

For more information on Table Structures check out this insights article.

Database Normalization and Table Structures

[Mr Hero]

A million thanks for your help. I appreciate your patience with me, as I am still learning and trying to wrap my head around Access.

[nico5038]

Patience is my middle name :-)

Success with your application !

Nic;o)

[Mr Hero]

I have made some progress, I ended up making some changes due to a recent step back... I do like the problem solve phase. However, It is the implementation part that always gets me confused. Is there way, I could convert some of this process into an event? or it is a best practice to use a qry in conjuction with VBA?

[nico5038]

You can't change processes into events, you need to use the event to trigger a process or give the user a button to activate a process.

Queries from VBA are in general slower as stored queries, thus I prefer to store them, but use VBA queries when there's too much flexibility needed.

Nic;o)

[NeoPa]

Nico:
Patience is my middle name :-)

That's right. How many times have I heard them say, while pointing at Nico :- "That's 'im. Patience."

[Mr Hero]

Thanks Nico! I appreciate you taking the time to explain some these issues.

I have a question, can I use type casting in a event to store without getting a data type mismatch?

[nico5038]

Looks obvious to me, that you make sure that the data to be inserted is in the proper format. In Access you use "nothing" for numbers, quotes (") for text and hashes (#) for the date datatype. The verification can be done by code, the type is determined by the used separator.

Nic;o)

[Mr Hero]

Thank you.