UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Docmd.runmacro, Access 2016    
 
   
kiwi
post Aug 14 2019, 11:19 AM
Post#1



Posts: 22
Joined: 26-July 18



Hello. I try to run a macro to find and display a record. docmd.runmacro "searchforrecord" but no record is found. I am using a combo as a criteria. If I just create the macro in the combo it finds the record, but not it code.
Go to the top of the page
 
GroverParkGeorge
post Aug 14 2019, 11:24 AM
Post#2


UA Admin
Posts: 35,676
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

Unfortunately, an answer to that problem isn't possible without enough detail to know what's happening.

Perhaps a more detailed explanation of exactly what you tried could be provided.

Or, perhaps a sample of the accdb to review.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
theDBguy
post Aug 14 2019, 11:24 AM
Post#3


Access Wiki and Forums Moderator
Posts: 76,311
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UA! welcome2UA.gif

Can you maybe post the macro code you're using? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
kiwi
post Aug 14 2019, 12:27 PM
Post#4



Posts: 22
Joined: 26-July 18



Sure can. SearchForRecord Object Type is blank Object Name is blank Record First Where Condition =="[ID]= "&Str(Nz([Screen].[ActiveControl],0))

I copied the Macro information exactly from the embedded macro. The embedded macro works. I just want it to work in code so I can use it as part of additional After Update events, especially on tabs.

I just started this project, and have only two tables, three forms, and one Macro. Two of the tables are actually nearly duplicates since I was trying out different concepts, but with the same results. I get a box to stop all macros.

From the uploaded database I would start from the frmSymptom. frmSymmptom1 is a near duplicate.
Attached File(s)
Attached File  FloralEssences.zip ( 67.91K )Number of downloads: 2
 
Go to the top of the page
 
GroverParkGeorge
post Aug 14 2019, 12:48 PM
Post#5


UA Admin
Posts: 35,676
Joined: 20-June 02
From: Newcastle, WA


Thanks.

For those coming from Excel, Word, etc. the distinction between "VBA" and "Macros" is minimal. However, an ACCESS macro is very different from VBA, so that may be a factor here.

Is that what you need? A VBA function to do the same thing as the macro?

Check into Attached File  macrotoVBA.jpg ( 23.64K )Number of downloads: 0

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
kiwi
post Aug 14 2019, 01:07 PM
Post#6



Posts: 22
Joined: 26-July 18



QUOTE
Is that what you need? A VBA function to do the same thing as the macro?


Yes, but I can't seem to get the convert macro to stop being grayed out!
Go to the top of the page
 
kiwi
post Aug 14 2019, 01:18 PM
Post#7



Posts: 22
Joined: 26-July 18



I did change the form record source to tblSymptom, but after I uploaded it to you. Now when I go to the create tab and then the macro button, the Convert Macros is no longer grayed out. When I select that, I get a message "There is nothing to convert". I created a new combo on my form that does an embedded macro. Still nothing to convert.
Go to the top of the page
 
kiwi
post Aug 14 2019, 01:28 PM
Post#8



Posts: 22
Joined: 26-July 18



I was finally able to convert the macro I created. I still can't see how to convert the embedded macro Access created. I set my combo box After Update event to call the function that the convert macro created and get an error of type mismatch. I tried doing a copy and paste of the code from the function into the after update event. It still won't run.
Go to the top of the page
 
GroverParkGeorge
post Aug 14 2019, 01:38 PM
Post#9


UA Admin
Posts: 35,676
Joined: 20-June 02
From: Newcastle, WA


We'll investigate your upload.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
kiwi
post Aug 14 2019, 01:42 PM
Post#10



Posts: 22
Joined: 26-July 18



QUOTE
We'll investigate your upload.


Thank you so much!
Go to the top of the page
 
GroverParkGeorge
post Aug 14 2019, 01:56 PM
Post#11


UA Admin
Posts: 35,676
Joined: 20-June 02
From: Newcastle, WA


I need to start with tables, I'm afraid.

You now have one or more symptoms for each remedy. Is it also possible for a remedy to apply to one or more symptoms?


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
kiwi
post Aug 14 2019, 01:58 PM
Post#12



Posts: 22
Joined: 26-July 18



Maybe eventually, but not right now. I really didn't want to get into many to many relationships, just wanted to have the record found in VBA through the After Update event.


This post has been edited by kiwi: Aug 14 2019, 02:00 PM
Go to the top of the page
 
GroverParkGeorge
post Aug 14 2019, 02:01 PM
Post#13


UA Admin
Posts: 35,676
Joined: 20-June 02
From: Newcastle, WA


I am going to say, then, that if it will POSSIBLY be the case in the future, NOW is the time to design for it. Nothing is harder than a rebuild of an existing relational database application after thousands of records have already been added under an obsolete design.

Put it another way. People are good with ambiguity, Computers are not.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
kiwi
post Aug 14 2019, 02:05 PM
Post#14



Posts: 22
Joined: 26-July 18



Makes sense. So what have I to do to make it right?
Go to the top of the page
 
kiwi
post Aug 14 2019, 02:13 PM
Post#15



Posts: 22
Joined: 26-July 18



Is there some way to have the forum update on my display when a new post is made? Otherwise I have to sit here and press F5 every couple of minutes. Is it possible to have email notifications as well? I'm just getting up here.
Go to the top of the page
 
GroverParkGeorge
post Aug 14 2019, 03:00 PM
Post#16


UA Admin
Posts: 35,676
Joined: 20-June 02
From: Newcastle, WA


Here's a revision. I added a Junction table so that you can relate one or more symptoms to one or more rememedies. This is a common scenario.

I also changed the filtering. We try NOT to use the approach previously found here. That is, instead of binding a form to a full table and using "Search" to navigage from one record to another, we filter the recordset in the form to the smallest possible number of records. This is important for performance and efficiency.

So, that's how I did this revision.

I also used a filter that I much prefer to most that I've seen. It involves TempVars instead of references to controls on a form. Take a look and post back with questions if that's not clear.

I like to use this filter to allow users to select any one record for a filter, or, if they choose to load all records (despite the advice to avoid that) it allows for that:

SQL
SELECT tblSymptom.SymptomID, tblSymptom.Symptom, tblSymptom.LargerDescription
FROM tblSymptom WHERE (((IIf([tempvars]![lngSymptomID]=0,0,[SymptomID]))=IIf([tempvars]![lngSymptomID]=0,0,[tempvars]![lngSymptomID])));


The way this works is that when a tempvar is 0, the filter is also 0, so all records are returned. If the tempvar is a non-0 value, records are filtered on that value.

BTW: this method means the query opens outside the form without raising an error.


Attached File  FloralEssences_Rev.zip ( 35.96K )Number of downloads: 1


There are other methods, similar to this which use control references on forms. They work well also.

But ultimately, we really should lean in favor of methods that limit the number of records returned to a form to the smallest possible number.

Oh, and you can also duplicate this design putting Remedies in the main form and Symptoms in the subform. That allows you to search by either remedies or symptoms and return the related records to the subform.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Aug 14 2019, 03:02 PM
Post#17


UA Admin
Posts: 35,676
Joined: 20-June 02
From: Newcastle, WA


Attached File  tracktopics.jpg ( 46.73K )Number of downloads: 0

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
kiwi
post Aug 14 2019, 06:01 PM
Post#18



Posts: 22
Joined: 26-July 18



Thank you a lot. I'm somewhat new to Access and had no idea about this kind of filtering or record selection. I will definitely study it and see if I can wrap my head around it. If not, I will take you up on the offer to get some other questions asked.

Thanks again
Go to the top of the page
 
kiwi
post Aug 14 2019, 06:03 PM
Post#19



Posts: 22
Joined: 26-July 18



Thank you on the options. I have done that now.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th September 2019 - 07:46 AM