Question Forms - Update records from a new window

[babui386]

Hi,

I am trying to UPDATE records from the Master form by clicking a button to a new window -

frmCustomerMaster (main form) - a button will trigger to UpdateCustomerMaster new window and pass all the data to the new window.

UpdateCustomerMaster (New window Form)
On Submit Update values.

frmCustomerMaster (main form) - is currently locked by properties, I do not want main form to be editable.

Any advise how to pass the data to new window and how to update the records?

Regards

Babui386
ALWAYS LEARNING

 

[whisp]

The only ways i know of are either using

• "openargs", a string that can be passed by docmd.openform
• global variables, where you can use any type, e.g. a whole recordset and/or multiple variables

 

[babui386]

Hi,

thanks, but any idea where to check global variables and how to use it, any tutorial's?

 

[whisp]

I'm not sure how much VBA knowledge you have, thus i stick to minimum information:

In the VBA editor you create a new module, you can call it Globals or whatever you like. There you declare your variables, starting with the keyword Global. For example: Global myNumber As Long. Also i'd recommend to prefix your variablename, so it won't conflict with any local variables that have the same name. E.g. instead of myNumber you call it GLB_myNumber. In the eventhandler where you open your new form, you set those global variables to the values you want (the same way you would set any local variable), and in the new form you can access those variables like any local variable.

Global GLB_myNumber as Long 'global variable declared in your "Globals" module


Private Sub myButtonThatOpensANewForm_Click() 'Eventhandler for the button you click to open the new form
    GLB_myNumber = 100
   
    DoCmd.OpenForm "TheNewForm", , , , , acDialog
End Sub


Private Sub Form_Load 'Eventhandler called when the new form opens
    MyTextBox.Value = GLB_myNumber
End Sub

There are a lot of VBA tutorials in the web, including tutorials about variable scopes. Best you search for them in google yourself, you know much better than me what information/knowledge you actually need respectively miss. If you never worked with VBA before you might need a lot of patience, looking for different stuff and solving a couple of other problems arising, until you get it to work, but it can be pleasing and help you in a lot of other scenarios.

 

[JHB]

Should the UpdateCustomerMaster form be bound or unbound?

 

[babui386]

Hi Whisp,

Many thanks for the explanation.

Well I am not a programmer, just new to it, trying to learn, got something in mind, and the scenerio I have given is a practice module for me.... Got sorted add new record, delete, export, now UPDATE, trying to find out how to it works, but as advised yes it would be good to understand the procedure...

So if I am not misunderstood here -

Global GLB_myNumber as Long 'global variable declared in your "Globals" module


Private Sub myButtonThatOpensANewForm_Click()
'Eventhandler for the button you click to open the new form

- Aplies to new module to declare Global variable

GLB_myNumber = 100

DoCmd.OpenForm "TheNewForm", , , , , acDialog
End Sub

- Aplies to frmCustomerMaster Form "lblUpdateRecord" button

Private Sub Form_Load 'Eventhandler called when the new form opens
MyTextBox.Value = GLB_myNumber
End Sub

- Aplies to frmUpdateCustomer Form in Form_Load

As JHB made a question should UpdateCustomerMaster Form be BOUND or UNBOUND?

Please help to explain... so that I can get it clear...

Many thanks in advance.

 

[JHB]

A bound form means a form that has a table or query as the RecordSource.
An unbound form means a form which has no RecordSource.

 

[Pat Hartman]

Wouldn't it be easier to have a lock/unlock button on the main form?

If you must use two forms, the second form should be bound. When you open it from the first form, use the Where argument to specify the primary key of the record you want to update. Intellisense and Help will give you details.

You will need to open the form as a popup so you can prevent people from clicking anywhere else until they close the edit form. Then back in the main form, the line following the DoCmd.OpenForm should refresh the current form to pick up your updates.
Me.Refresh.

This is basically 2 lines of VBA since Access does most of the work for you.

 

[whisp]

The Eventhandler Private Sub myButtonThatOpensANewForm_Click() is not part of the module, it's part of your master formular, otherwise you're right. After selecting your button in your form, click the "Event"-Tab in the properties. Click the three dots by the "on click" event and a choice will appear. Chose Code Builder and the eventhandler (private sub - end sub) will be created for you. Then you just need to add the code inbetween the private sub - end sub).

Also, if you want to pass some data back to your main form, you might want to set some global variables in your new form, e.g. in the click eventhandler of your ok-button. You could also do it in the form's on close event, but this would also be called if you close your form by pressing a "cancel" button e.g.. To access that data from your mainform, just read the global variables after the DoCmd.OpenForm, since that's where the code will continue after the new form is closed (as long you opened it using acDialog).

If you can or want to update your record in your new form directly, Pat's solution is probably easier to implement, also global variables have the disadvantage that they're accessible and changeable anytime from anywhere.