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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.


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 38

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 38

Expert Comment

ID: 39704887
You are quite welcome:)

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

635 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