Solved VBA to Change Data on Another Form (1 Viewer)

Weekleyba

Registered User.
Local time
Today, 05:00
Joined
Oct 10, 2013
Messages
586
I have a database with multiple forms. Forms are F_Project, & F_Contract.
On F_Contract I have a textbox called AwardDate.
On F_Project I have a checkbox called ActiveAwarded.
What I want is, when the AwardDate is entered (or deleted), I want the ActiveAward changed to True (or False).

The F_Contract and F_Project have tables T_Contract and T_Project with unique IDs, ContractID and ProjectID.
I have query that relates the two IDs called TJ_ProjectContract.

To complicate it a little more, the F_Project may or maynot be open when the F_Contract is open.

I'm thinking an AfterUpdate event on the AwardDate textbox but not sure how to do it.

Is that enough info?
Thanks for any help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:00
Joined
Oct 29, 2018
Messages
21,357
On F_Project, if you put a check in the ActiveAwarded box, did you also want to automatically put a date in the AwardDate on F_Contract? And if you uncheck the box, do you want to automatically remove the date?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:00
Joined
May 7, 2009
Messages
19,169
on afterUpdate event of AwardDate:
Code:
Dim projID As Variant
Dim rs As DAO.Recordset
'get the projectid
projID = Nz(DLookup("projectID", "tj_projectcontract", "contractid = " & Me.contractid), 0)
'check if form is open
If SysCmd(acSysCmdGetObjectState, acForm, "F_Project") <> 0 Then
    'form is open
    Set rs = [Forms]![F_Project].Form.RecordsetClone
    With rs
        .FindFirst "ProjectID = " & projID
        If Not .NoMatch Then
            .Edit
            !ActiveAwarded = Not IsNull(Me!AwardDate)
            .Update
        End If
    End With
    Set rs = Nothing
   
Else
    'form not open, use SQL update query
    CurrentDb.Execute "update T_Project Set ActiveAwarded = " & (Not IsNull(Me!AwardDate)) & " Where ProjectID = " & projID
End If
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:00
Joined
Sep 21, 2011
Messages
14,038
Just curious, wouldn't you have to refresh the form?
 

Weekleyba

Registered User.
Local time
Today, 05:00
Joined
Oct 10, 2013
Messages
586
DB, that's a good thought but I think I want them to actually go to the F_Contract and edit it there.

Arnelgp, Wow! I'm going to try that right away. AND study some of those code lines that I need to learn and understand.
Many thanks guys!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:00
Joined
Oct 29, 2018
Messages
21,357
DB, that's a good thought but I think I want them to actually go to the F_Contract and edit it there.
So, are you saying the user should not be able to check or uncheck the box on F_Project?
 

Weekleyba

Registered User.
Local time
Today, 05:00
Joined
Oct 10, 2013
Messages
586
Correct. I will disable it.
I think that will work.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:00
Joined
Oct 29, 2018
Messages
21,357
Correct. I will disable it.
I think that will work.
Maybe, here's a better approach. Use a query joining the contract table to the project table and add a calculated column like:

Code:
Active: IIf(IsDate(AwardDate), True, False)

And display that column on the form instead of the field ActiveAward.

Just a thought...
 

Weekleyba

Registered User.
Local time
Today, 05:00
Joined
Oct 10, 2013
Messages
586
Very nice! Didn’t know about IsDate function before. Yes, that’s a better way.
thanks!
 

Weekleyba

Registered User.
Local time
Today, 05:00
Joined
Oct 10, 2013
Messages
586
I'm getting an error with the code from arnelgp.
"Compile error in Access user-defined type not defined"
I tried setting the reference library to Microsoft DAO 3.6 Object Library, but when trying to do so, give another error, "Error in loading DLL"

Anyone know how to fix?
 

Weekleyba

Registered User.
Local time
Today, 05:00
Joined
Oct 10, 2013
Messages
586
I've tried the following without success.
Can anyone lend an assist?
I'm using MS Access 2019.



1621819763857.png



1621819823640.png




1621820013729.png



I get this error when trying to load the Microsoft DAO 3.6 Object Library.
And it will not load.

1621820147449.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:00
Joined
May 7, 2009
Messages
19,169
Add reference (in vba, tools->reference) to:

Microsoft Office #.# Access Database Engine Object Library
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:00
Joined
Feb 19, 2002
Messages
42,970
Some things to remember.
1. Forms don't store data. Tables store data.
2. Storing data in multiple places violates second normal form.
3. Having a flag as well as a date is a duplication of data.
4. You can only reference controls on a form that is open.

If the data had been properly stored, at most you would have needed one line of code to reqquery the other form.

Forms!OrginalForms.Requery
 

Users who are viewing this thread

Top Bottom