On Focus event of Form (1 Viewer)

[AnilBagga]

I have 2 fields in a form which are supposed to store values when user enters data. The 2 events in after update event of the 2 fields (UncoatedGSMID and ) in the CoatingGSMID1 form are as below

Me.UncoatedGSMID = Me.UnGSMID
Me.CoatingGSMID1 = Me.CGSMID1

The events work fine. The problem is some entries have been done by user w/o pressing the tab and the event has not been triggered. I wanted to add these 2 events in the form events such that these events trigger whenever form is opened

I added these events in form events of On Load and On Got Focus, but the functions dont work


I am unable to add it on Open event of the form and get an error

Where do I add these functions?

Thanks in advance for the help

 

[G37Sam]

The real question you should be asking yourself is, why are you duplicating this data? Can you not just have them link to the same source?

AfterUpdate only updates after the table is updated
On_Change could also work, but this will require use of [field].text property to capture the actual text before it updates

Or you could just have an unbound text field =[field] in its source

 

[AnilBagga]

The real question you should be asking yourself is, why are you duplicating this data? Can you not just have them link to the same source?

I tried . The unbound control has a dlookup as below and I could not include this in the query (record source of the form). I am unable to writeSQL for queries and can only do the Queries in Design view and hence the limitation and this workaround , if one can call it this

=DLookUp("GSMID","tblfabricGSMID","[UncoatedGSM] Between [tblfabricGSMID].[GSMMin] And [tblFabricGSMID].[GSMMax]")

 

[G37Sam]

on the form itself, just do =[GSMID]

it will help if you share a sample database

 

[AnilBagga]

on the form itself, just do =[GSMID]

it will help if you share a sample database

where do I enter this text? in an event?

 

[Minty]

Just as a FYI a form cannot get focus if it has any controls on it.

The control always receives focus, not the form.

 

[AnilBagga]

Just as a FYI a form cannot get focus if it has any controls on it.

The control always receives focus, not the form.

In other words I cannot add the 2 events anywhere such that when the form opens, these 2 events occur for all records

I did try me.refresh and ,me.recordset.requery on load, on current events of the form, doesn't help

 

[Minty]

Yes, you can - the current event seems the obvious place as it fires whenever you move around the records.

Form Open won't allow you to update values as the recordsource won't be set at that point, so the Load or Current events would be the place.

However, this would be simple to join in a query and therefore avoid the problem.
Post up a zipped, stripped down a sample of your data and the forms concerned.

 

[arnelgp]

add a button on the form that will "re-calculate" the two fields.
looping though the recordset of the form and calling your two events.

 

[bastanu]

In your query can you try this updated dLookup?
UnGSMID: DLookUp("GSMID","tblfabricGSMID",[UncoatedGSM] & " Between [tblfabricGSMID].[GSMMin] And [tblFabricGSMID].[GSMMax]")
Assumes you have a field in the query named [UncoatedGSM] whose value you use to extract the id from the range.

Cheers,

 

[AnilBagga]

In your query can you try this updated dLookup?
UnGSMID: DLookUp("GSMID","tblfabricGSMID",[UncoatedGSM] & " Between [tblfabricGSMID].[GSMMin] And [tblFabricGSMID].[GSMMax]")
Assumes you have a field in the query named [UncoatedGSM] whose value you use to extract the id from the range.

Cheers,

Thank you Vlad

 

[AnilBagga]

Thank you Vlad

The UncoatedGDMID is stored in the table as it is used in many other queries and if it is a field in the query, I would need to recall this query i/o table making it slow. Hence the need to have the events to be able to store the results of the Dlookup's

It seems having the 2 lines of code in a command button in the form is possibly the best solution

 

[bastanu]

Hi Anil,

Another option would be to set up an Update query to update all those values and run that in the open or load event of the form, that way there is no need to click a button. As for the current record I would use the BeforeUpdate event of the form to update the current record as for new records the Current even will not work.

Cheers,

 

[Pat Hartman]

The QBE can build the query you need. Add both tables. Draw the appropriate join line. Change the join type from inner to left.

WARNING - Whenever you bring in extra fields from a lookup table using this method, it is IMPERATIVE that you LOCK the control to prevent accidental changes.