Update Form to perform On Current event 3

[SOMSteve]

In an Access form, I used the On Current Event to limit the visibility of a few different buttons and text boxes based on the values in a couple different fields on the form. While it works perfectly if you move off the record and then back on, I want it to work as soon as the user selects a value that triggers the On Current Event to make the buttons/text boxes visible (or not visible). Maybe I didn't search for the right words, but I was unable to find a thread that discussed this issue directly. I'm guessing that a simple "After Update" event on each of the fields that affect the On Current event in the form will do the trick, but I don't know what to put to make it work. Any help on how to get the whole form to update without moving off the record would be great, thanks!

 

[jazzz]

Use the Form on dirty event to turn the on.

Life's a journey enjoy the ride...

jazzz

 

[Remou]

The easiest thing to do is to create a Sub that checks the various items and sets up the buttons. This can then be called from the After Update event of each relevant control as well as the Current event.

 

[PHV]

a value that triggers the On Current Event
The Current event is only triggered once per record, so I quite don't understand what you mean.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SOMSteve]

Hi PHV,
I should have probably put my coding in here to help clarify the example (since I don't always describe it all that well):

Code:
Private Sub Form_Current()
If Me!ItemType = "Vehicle" Then
Me!cmdOpenfrmItemVehicle.Visible = True
Else
Me!cmdOpenfrmItemVehicle.Visible = False
End If
If Me!ItemType = "Equipment" Then
Me!cmdOpenfrmItemEquipment.Visible = True
Else
Me!cmdOpenfrmItemEquipment.Visible = False
End If
If Me!TransferType = "Surplus Store" Then
Me!InventoryNum.Visible = True
Else
Me!InventoryNum.Visible = False
End If
End Sub

This makes certain buttons (cmdOpenfrmItemVehicle & cmdOpenfrmItemEquipment) as well as the text box for Inventory # visible when the conditions set in the "On Current" event is met. When a person goes to a new record and selects "Vehicle" for example, they would have to tab out of that record and then back into it to see the cmdOpenfrmItemVehicle button appear. I'm trying to keep them from having to do this (i.e. make the button appear as soon as they select "vehicle"). Let me know if this helps to clarify, thanks for such a quick response from everyone!

 

[SOMSteve]

Hi jazzz, I tried moving the code to the "On Dirty" event and while it did work while adding new records, I was unable to use it in combination with "On Current" event (thus going back to records with the info already in there did not display the buttons/text boxes that I needed). Should I be modifying the code from how it is set up in the "On Current" event in order for it to work with the On Dirty? I am still new to coding so I apologize for what may be a simple question. Thanks.

-Steve

 

[jazzz]

After Update of the ItemType and TransferType will make that happen. You can place the same code in the event for ItemType. Now put the TransferType in the afterupdate event for that control too.

If you know how to use a sub create them and call it whenever you need them on the afterupdate event for the control (less typing).

Life's a journey enjoy the ride...

jazzz

 

[jazzz]

You still want the code in oncurrent you just want the event to fire once you make the change to the control and in this case it will be afterupdate.

Life's a journey enjoy the ride...

jazzz

 

[SOMSteve]

Thanks Jazzz, that worked perfect (after trying it out both ways I figured out that it had to be in On Current still). The only issue I still have left is with the part of my code that references the field TransferType. This field is actually based off a query to determine what sort of transfer the item was engaged in (can be involved in multiple different types, but only care for this field if any one of those is "State Surplus"). If the transfer is equal to "State Surplus" then the extra text field appears. When I update the subform for the item with which transfers have taken place, the code in the After Update event doesn't work because it never has been given focus. If I move off the record and then back on, the field appears (probably because of On Current). Any ideas on how to work around this?

 

[PHV]

You may have such code :

Private Sub doButtonsVisibility()
Me!cmdOpenfrmItemVehicle.Visible = (Me!ItemType = "Vehicle")
Me!cmdOpenfrmItemEquipment.Visible = Me!ItemType = "Equipment")
Me!InventoryNum.Visible = (Me!TransferType = "Surplus Store")
End Sub

Private Sub Form_Current()
doButtonsVisibility
End Sub

Private Sub TransferType_AfterUpdate()
doButtonsVisibility
End Sub

Private Sub ItemType_AfterUpdate()
doButtonsVisibility
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SOMSteve]

Thanks PHV, that saves a lot of coding.
I still have the same issue with the TransferType field, however, since it is based on a query. Also, I got a "Run-Time error '13': Type Mismatch" as I was scrolling through my records to test out the new code. I appreciate the help!

-Steve

 

[SOMSteve]

I meant to let you know that the run time error was pointing to the line:
Me!InventoryNum.Visible = (Me!TransferType = "Surplus Store")

 

[PHV]

since it is based on a query
???

Which kind of control is TransferType ?

 

[SOMSteve]

TransferType is based off a query that only brings up "State Surplus" transfers and then feeds into the Items form. I brought this field in to determine if the item was engaged in at least one "State Surplus" transfer type. Because one item can be involved in multiple different types of transfers, this field is tracked in a separate table. The only reason for it being in the form is to be used as a control. These different transfer types for the item are affected when a user inputs them into the "Transfer Item" subform (located within the Item form). It works as a control, but still requires them to move off the record and then back on in order to make the "Inventory #" appear on the form.
I hope this helps answer the question, let me know if you need anything else to assist?

 

[jazzz]

I believe what PHV is asking is what data type is it? I am guessing that it is numeric, hence error 13, or vise-a-versa. Let us know what the datatype is and how are you bringing it into the form. Is it on a join that is numeric?

Life's a journey enjoy the ride...

jazzz

 

[PHV]

SOMSteve, again, what is TransferType ? a TextBox, a ComboBox, ... ?
What is its ControlSource and/or RowSource ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SOMSteve]

Sorry for my misunderstanding on what you were looking for, thanks for the clarification Jazzz and PHV.

TransferType was a textbox; its data type was a text field. It was brought into the form using a join on Item # (the unique key for the Item table) between the Item Table and a query that had only two attributes, Item # & TransferType.

I have decided to only use the other two events that I learned about earlier in this discussion and not implement the one based off TransferType (the one we were just working on) to keep things relatively simple. As a new person to coding I really appreciate having the support of knowledgable people, so thanks for all your help on this!