Macro to conditionaly apply a value to a control

Posted on 2013-12-06
Last Modified: 2013-12-08
I have a form frmCompanies on which a record ComID is selected.

Then I open a form frmNotes with a macro filtering all notes in tblNotes where ComID = frmCompanies.[ComID].Value.

Now when I decide to make a new note, I want the frmNotes.[ComID].Value must also be set equal to frmCompanies.[ComID].Value.

I was thinking to use On Dirty as trigger to achieve this. However I don't find a way to allocate a value of a control from another form to a control on the active form.

I do not want to use a subform.

I do not have experience with macros, so this is the first one. I know VBA.

Any help please.
Question by:Fritz Paul
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 30

Expert Comment

ID: 39700627
Upload a sample database,  removing any object but the ones needed to show the issue.

Author Comment

by:Fritz Paul
ID: 39700680
Attached is the applicable database. It will open with frmCompanies and if you click on "Open frmNotes" button, the second form will open.

LVL 30

Expert Comment

ID: 39700765
Try this:

Private Sub Form_Dirty(Cancel As Integer)
    Me.ComID = Me.Tag
End Sub

Private Sub Form_Open(Cancel As Integer)
    Me.Tag = Me.ComID
End Sub

Open in new window

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

by:Fritz Paul
ID: 39700793
Hi, Sorry if I created a misunderstanding.

I specifically want to use a macro for this purpose, because I want the experience and I want to create a web database.

Thanks for your attension. Can you help with an Access 2010 macro?
LVL 37

Accepted Solution

PatHartman earned 500 total points
ID: 39703656
If you are creating a web database with A2010, you are wasting your time.  That technology is already deprecated.  The A2013 version of web databases is quite different.

Use the BeforeInsert event.  You only want to populate the ID if this is a new record.  Use the macro builder to build it.

Author Closing Comment

by:Fritz Paul
ID: 39704264
Thanks for your comment about A2010 web databases and
your answer to my question.
Also thanks for the other contributions.
LVL 37

Expert Comment

ID: 39704887
You are quite welcome:)

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question