form text field update a different table column as well

[mrbeepa]

I have a form where a sales person enters an installation date, called installDate in the db. I need the date to be formatted into a week number in a different column of the table as well as updating it's on source. I am unsure how to do this. Everything I have tried using the expression builder has not worked? I am not really up on the syntax of VB though I was trying to write a sub that contained a function to do this. (unfortunately I couldn't figure out how to reference the form and text fields in the VB code subroutine!) I am open to any suggestions.

I first tried using: Format([installDate],"ww") in the table field properties for that column but that failed and inquires on another forum told me that was not the place to do it. Hence I am trying to do it when the data is entered in the form by the sales rep. I tried entering the same code in the afterUpdate property of the textInstallDate textbox where it is entered and that didn't work? Any suggestions as to an approach or the syntax to reference the form and textfield in VB would be greatly appreciated!

 

[KenHigg]

If you use something like the following in the underlying query would it work:

WeekNumber: DatePart("ww",[mydate])

 

[mrbeepa]

That worked for the first situation

If you use something like the following in the underlying query would it work:

WeekNumber: DatePart("ww",[mydate])

Thank you for that idea, that definitely worked in the first instance in which I needed to do that.

Now I have one more where essentially I am trying to do the same thing.

I have the same form that the sales rep plugs in the buyers information. name, street address etc. the method I use to create a contract number in the field before an inhouse one can be assigned is to take the the street numbers and add them to the phone number to create a contract id. It is simple to add the two together, that I can and have done, but only in another text box that I have to cut and paste into the contract id text box. I'd like to have it automatically fill into the contract Id text box and still have the control of that textbox be the table column contractID so that it affects that column in the db.

I thought this might have to be done with a subroutine but I have no idea how to format the subroutine. I am familiar with several programming languages but not VB and I have no experience accessing forms or textboxes on forms in a VB environment ( or any programming language for that matter!)

Again thank you for your assistance! Any and all help greatly appreciated!

 

[DCrake]

Simple Software Solutions

You are in danger of overwriting assigned contract id's with temporary contract id's

If you contract id control is bound to the contract id in the table it will show that. if you then add a new contract and use the calculated contract as a temp id the only way to do that is to use a before/afterupdate event to populate the contract id.

What I would be tempted to do is to use the OnCurrent Event of the form and get it to check if there is a value in the contract id field if not then use the calculated values to make up a temp contract id and paste it to the contract id text box.

One thing to consider is how long does it take from adding a new contract with a temp id to actually assigning it an assigned id?

Any additional work recorded against the new contract will be linked via the temp id and as such when the new assigned id is applied you will need to cascade the db to update the temp id.

 

[mrbeepa]

Thank you CDrake

Thank you DCrake for your suggestion. I am very new to working in Access can you give me an example of how to code the OnCurrent Event of the form to check a for the value in the contract table?

You are in danger of overwriting assigned contract id's with temporary contract id's

If you contract id control is bound to the contract id in the table it will show that. if you then add a new contract and use the calculated contract as a temp id the only way to do that is to use a before/afterupdate event to populate the contract id.

What I would be tempted to do is to use the OnCurrent Event of the form and get it to check if there is a value in the contract id field if not then use the calculated values to make up a temp contract id and paste it to the contract id text box.

One thing to consider is how long does it take from adding a new contract with a temp id to actually assigning it an assigned id?

Any additional work recorded against the new contract will be linked via the temp id and as such when the new assigned id is applied you will need to cascade the db to update the temp id.