Manipulating table data without relying on invisible controls

[klfduncan]

Hi all,

Ref: I am using Access 2002

OK. I want to change the data in a table (LeadInfo), which has 60 fields and populates the form LeadForm . The only way I can do this at the moment is to have a control in the form, non-enabled and invisible and using some form of AfterUpdate Event:

Me!DateLastUpdate = date()

I'm pretty sure that there must be a way to do this programatically instead of having to rely on hidden controls? I've tried opening recordsets and defining objects and all sorts of things designed to infuriate me, since I only have the help files for guidance...

Thanks for any help,

KLF

 

[VBslammer]

You can reference any field that belongs to the Recordsource of your form, whether there is a bound control for it or not. So if your form's recordsource is a table (as opposed to a query) you can reference all the fields in the table directly:
[tt]
Me!Field1 = Date()
Me!Field2 = "Hello World"
...
Me!Field60 = "#1/1/2005#"
[/tt]
Many developers leave their bound control names the same as the underlying field name, so Me!Field1 could reference both the field and a textbox control - which is rather ambiguous. I prefer to name all my controls using a naming convention that removes the ambiguity, such as txtField1, so Me!Field1 is not the same as Me!txtField1.Value.

I'd update the fields in the Form_BeforeUpdate() event though...

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[klfduncan]

Thanks VBSlammer. I was unaware of that and thanks for the tip on naming conventions though I may be a bit far down the line for changing those at the moment.

Right. Now for the (at least what I think) toughie.

LeadForm is populated and controlled by LeadInput. Is there any way which I can manipulate the data in the 'Contracts' table.

E.g. LeadForm control 'RefNum' is inputted as KORE1001 (which is in table 'LeadInput', field 'RefNum'). I'd like to write KORE1001 to table 'Contract', field 'RefNum'

At present, I've had to create invisible, non-enabled subforms. Once again, I really do think there has to be an easier, more efficient and altogether better way than this.

Thanks again,

KLF

 

[VBslammer]

You can update the other table dynamically with an action query - so long as you have a way to identify its unique key. Pick an event to do the update and run the query:

Private Sub Form_AfterUpdate()
  DoCmd.RunSQL "UPDATE Contract SET [RefNum]='" & Me!RefNum & "' WHERE ContractID=" & Me!ContractID
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[klfduncan]

Thanks VBSlammer. I haven't been able to put that into the new DB yet, as I'm rather busy but it looks great.

Thanks for your help and I'll reply if there are any problems. If not, I gratefully appreciate your help. But now... I'm off to the HK7s to drink myself silly, so don't expect any work related enquiries for about three weeks!

Cheers,

KLF