CODEWITHSUNDEEP
vbadatabasems-access
Sotiris Kettenis
Sotiris Kettenis

Reputation: 49

How can I pass data between forms?

I have this form below:

frmMembers

As soon as I press the button "New Payment" I want the form to take the values inside the member ID text box, the first name text box and the last name text box and automatically load them to the fields respectively on the form below:

frmPayments

I'm thinking of doing it with global variables but I've never worked with VBA. Can someone explain to me how to do it?

Upvotes: 0

Views: 193

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49329

As several noted, if you use a tab control, you can put that form as a sub-form, and then in most cases no code at all will be required.

However, there are quite a few ways to pass values.

The cleanest code, and no global variables?

I suggest this approach:

You don’t pass the values, but “grab” the previous form object.

So, in your 2nd forms code module, you declare a variable called frmPrevious. This var is declared at the forms module level.

So, you code module will look like this:

Option Compare Database
Option Explicit

Public frmPrevious     As Form

Now, in the forms on-open event, you do this:

Private Sub Report_Open(Cancel As Integer)

   Set frmPrevious = Screen.ActiveForm

End Sub

Note that your current form does NOT become the active form until the open event is completed. In fact, you can even put the above code in the forms on-load event.

Ok, now.

Your new payment button code?

With the above, you are free to grab “any” values you want from the previous form.

Eg:

Me.CompanyName = frmPrevious!CompanyName
Me.member_id   = fromPrevous!ID

Etc.

So, the above is nice, since then you can pass/use and reference ANY value or control from the previous form.

However, you should not be re-copy the LastName box, but in fact ONLY set the customer_ID or some such to the customer, and then you can in the future reference/gget phone number, city etc.

Re-copy of a value is very very BAD approach, as you are breaking the relational design, and you wind up writing tons and tons of more code then is required.

IN fact, in your code, likely the new payment button has to go to a NEW record FIRST, else the above code will start to overwrite existing values.

And, if the user hits new payment, and THEN decides to bail out and not add? Well, then you going to wind up with blank records.

What this really means is you should put the code that “copies” and “sets” the values from the previous form in the before insert event. This event ONLY will fire for new records, so you don’t need special code to check or worry if you going to accident over-write values in a known good record.

Upvotes: 1

Related Questions