How To Correctly Set Fields That Would Be Passed To Macros
Apr 15, 2013
I currently have a form that asks for 2 fields to be filled with data to be passed to 3 separate macros that will then run a set of about 10-15 queries based on the values entered in the form. This used to be sufficient as the manually entered data was only a small set. Now the number of values that need to be run through the macros is starting to become too large to constantly be changing the values and running the macros. I can set up a table that will contain all the value combinations that would need to be entered into the form to then be passed to the queries, but how can I set this up to be done automatically. I know it can be set up using a loop, but I'm not familiar with VBA code (most of my access work is done using the design tools) so I'm not sure how to correctly set the fields that would be passed to my macros. I can also deal with the values being passed to my form one at a time, then have the macro run on that set of values, then have the next value sent to the form, etc . . .
I have a table set up with data for all stores that I'm querying. I need to select the REPORT_BRAND and store_no from this table and run about 10-15 queries that will sort the data properly for a dedupe that will be performed at the end. The form that is set up asks for the REPORT_BRAND and store_no, then it gets passed to the macros which will create the tables for store #1. The values in the Form then get replaced with the next report_brand and store_no and the macros get run again for store #2. At the end of all the stores, I have 2 tables setup with all the final results for each store. These 2 tables then provide the results that I then manipulate to fill in the reports that are required. I need to run each store separately because the results as based on total record percentages by store.
I want to remove the manual intervention from the procedure and just have a table feed the form (or eliminate the form all together) and have the macros run for all stores on my table.
I've searched around and have seen a lot of examples of using a loop, but not being familiar with VBA makes understanding exactly what I need to enter for my specific data a bit difficult.
View Replies
ADVERTISEMENT
Jun 10, 2005
:D
I have a form set up where a user chooses a page tab. Either statistical reports or detail reports. Then they have to choose between 2 toggle buttons. Candidate or hire.
So a user chooses detail, then candidate. An option group below that shows the different groupings of detail candidate reports that they can choose.
A couple of examples - detail report of candidates by office applied to, department, application source etc.
Once that is chosen a button is visible to continue. Now they are taken to the appropriate form. This is done by a global variable gstrformname defined in the afterupdate of the group option that they chose (office etc.)
Once they click to continue to the criteria form, they are choosing the criteria. (for example purposes) the choice below takes the user to a criteria form for choosing an application source ( email, monster.com etc) or click a button and all types are given. I have all of that set up. But now I want to change what report the command button is opening, to a variable. A variable that would be set by the below form when the type of report and grouping for the report are chosen...that defines a certain report.
I specified on this main report menu that it is a candidate report that I want, but when I get to the criteria form, I need a way to pass that choice on. I am not sure how to best do that.
I am trying to avoid making 2 copies of the same form 1 for candidates and 1 for hire. :crazy: I undertstand that I should pass a variable here.
So basically on the main form they choose candidate or hire report, then they choose group. The grouping information tells the command button what form to open (the application source criteria form, the office criteria form, etc). The thing is, this form is the criteria for the candidate report and the hire report. The command button on the criteria form would ideally be -
docmd.previewreport gstrreportname, acpreview
my question is, how or where do I define, from the point that they choose candidate or hire, to the next form, that they want the candidate by office or hire by office report? (define the gstrreportname variable as one of those too.)
Make sense?
Here is a pick of the report form (menu)
http://www.geocities.com/misscrf/reportmenu.jpg
For the afterupdate of the frame for the grouping of the report, I have a select case. In that I define gstrreportname for the report it is, if the choice goes right to a report. If it goes to a form first (for criteria), I define that form with a global variable gstrformname. Can I also define the report there (gstrreportname)? If I do that, will the criteria form know that? Even if I close the main report form?
I hope someone understands me! :shrug:
View 5 Replies
View Related
Jan 15, 2008
Okay I created a database that I am going to use to collect data about a study we are doing.When a participant is added to the database on the main (Participation Details form), it automatically generates an ID and creates a record in a second table/form - a questionnaire (i.e. Athens Questionnaire) that the participants fill out preserving the ID number that was created in the participation details form (I have been able to achieve this).However what I wanted to do next is be able to filter the Athens questionnaire using a combo box which contained all the participant's ID's.I was able to create the combo-box and populate it with the ID's using a query and did not put a control source on it (i used =tblAthens.ParticipantID ....) iand created a sub-form that contained the questionnaire. I did this by creating the questionnaire in a separate form called frmAthens_sub and dragging it from the "explorer window" into my open Athens Questionnaire form. However when I attempted to place code in the "After Update" field of the combo box I keep getting errors and I am not sure why.The code I am using is:Option Compare DatabaseSub SetFilter() Dim LSQL As String LSQL = "select * from tblAthens" LSQL = LSQL & " where ParticipantID = '" & cboSelected & "'" Form_frmAthens_sub.RecordSource = LSQL End SubPrivate Sub cboSelected_AfterUpdate() 'Call subroutine to set filter based on selected ParticipantID SetFilter End SubPrivate Sub Form_Open(Cancel As Integer) 'Call subroutine to set filter based on selected ParticipantID SetFilter End SubIt is highly possible that I am just not doing something right as this is the first time I have used sub-forms. I tried to attach a copy of the database but cant get it below the max. The error seems to be coming from this line --> Form_frmAthens_sub.RecordSource = LSQLIs there an easier way or alternative code for filtering a sub-form from a combo-box?
View 3 Replies
View Related
May 7, 2007
Not sure if I am asking the right question, but....
I would like to pass a string variable to a query but it does not seem to work.
In the query, my criteria for the date field is (and works):
>=[forms]![frmDisposition]![FromDate] And <=[forms]![frmDisposition]![ToDate]
But, this is not working for the ID field criteria:
[forms]![frmDisposition]![ID]
In the forms code, I have a string based on the result of 3 check box. I tried using an unbound (ID) control to display the string so I know that the value of the string is correct. EX: "FW" Or "MA" Or "PD"
Is it possible to pass the value of the string to the query or do I need to try and pass the value of the unbound control to the query?
View 6 Replies
View Related
Aug 10, 2007
Dear Access Query Expert
I have created a query which has a function call as the criteria for one of the numerical fields. The function returns a string expression such as .....
1) 132 OR 142 OR 156
2) 132 OR 142
3) 132
..... into the criteria section of the numerical field.
Unfortunately, the query doesn't work if the criteria is generated by the function call. However, if I hardcode the criteria (don't send a function call but directly write 132 OR 142 OR 156) the query works.
I am puzzled and I am not sure how to solve this problem.
Thank you so much.
View 1 Replies
View Related
Sep 11, 2005
I am writing a database with a number of tables with forms attached to each table. I have placed navigation buttons on each form to move to first, last, next, previous. I have set up the code associated with the buttons as "public sub" so it is available for each form to use.
This was working OK except that when I used the NEXT button at the last record it would open a new blank record & keep opening a new blank record on each click.
To over overcome this I put in an If statement to check if it had moved to a new record. To do this I used the following:
If Me!NewRecord Then ..... etc
Now I get an error "Invalid use of Me key word" I suspect it is because I am using ME in a public sub
How do I overcome the problem of moving passed the last record and still have the coding available to all forms?
The Code is below.
Thanks for any help.
Public Sub Next_Record_Click()
On Error GoTo Err_Next_Record_Click
DoCmd.GoToRecord , , acNext
If Me!NewRecord Then
' If new record move back to previous
DoCmd.GoToRecord , , acNext
' Send message
MsgBox "This is the last record", , "No More Records"
End If
Exit_Next_Record_Click:
Exit Sub
Err_Next_Record_Click:
MsgBox Err.Description
Resume Exit_Next_Record_Click
End Sub
View 3 Replies
View Related
Oct 29, 2006
I have a combo box that has 3 columns. What I am trying to do is get the third column to be passed as the value to another textbox after the combo box is selected.
combo box sample data: Male/John/NY
the 3rd Column which is state I would like to have it sent to the State Field...
View 2 Replies
View Related
Apr 18, 2013
Instead of the combo box value being passed into the target query as it should I *always* receive a popup that states "Enter Parameter Value"
I set my filter criteria as such in the target query: "[Forms]![Invoice_Form]![comboFamily]" but for some reason, despite there being a value in that combo box, I am always prompted for a parameter. VBA code associated with a "Generate Invoice" button on the form causes the query and the report to open.
I've attached the database, everything is dummy data.
The suspect form is the "Invoice_Form" the suspect query is the "Invoice_Query" and the suspect report is the "Invoice".
If a number is entered in the "Enter Parameter Value" field that matches the primary key of a family in the "Family" table then a report is generated correctly (as it is currently configured). For some reason it seems that the form with the combo box just cannot be accessed by the query.
View 4 Replies
View Related
Jan 29, 2015
I am new to access. I have created a form in which I used a combo to show Main Area Name. this value is passed into a query to show only area under main area only. Created another combo to activate the query to show the area.
View 6 Replies
View Related
Apr 15, 2013
I have a form with numerous fields on it, with the one I'm concerned about being a date field.
I want a different form to open automatically if the above date field is in the past (this will act as a warning to the user).
I've created the warning form as a pop-up view but just want to know the best way to have it open automatically.
Can this be done via a macro in the OnOpen Property (or maybe OnLoad) or will this have to written in vba.
View 7 Replies
View Related
Dec 3, 2014
I have a form that allows you to search for records and displays all the information for that project, typical stuff.
On this form is a subform with the followings Fields:
Inspection Requested, Inspection Due, Inspection Done, Overdue
When you update inspection requested the inspection due is automatically updated to 30 work days from that day.
Now what I'm trying to do is get the overdue field (it's a yes/no field) to automatically check itself when the current date is passed the inspection due and the inspection done field is blank.
Here's what I have that isn't working:
Code:
Private Sub Form_Current()
If DateAddW([REQUEST], 30) < Date And [INSPECT DONE] = 0 Then
[OVERDUE] = True
End If
End Sub
DateAddW is a UDF that works just fine. I've tried replacing 0 with Null and neither works.
View 4 Replies
View Related
Sep 26, 2014
I've got a private sub that runs when the form loads. I want it to get the id of the record which has opened this new form.
The public sub populates the variable but it's Empty going back into my private sub. I'm sure its a simple school boy error but it has me stumped!
I want it public as I need to keep that id as new records are added in the form.
Private Sub Form_Load()
FRStudentFundingRequestID
'fundingrequest = FundinRequestID
Me.txtFKFReq = FundingRequest
End Sub
__________________________________
Public Sub FRStudentFundingRequestID()
FundingRequest = [Forms]![frmFundingRequest]![txtpk]
End Sub
View 3 Replies
View Related
May 6, 2014
I am making a database for my work place where there is telephone counsellors and they need to complete a certain number of supervision hours and a few other categories of hours required for training and several other things. The manager wants to be able to see the total supervision hours and the other categories for a worker when the worker is selected and the date range for the queried time entered.
I have a form that has quite a few items on it. I have two text boxes that allow me to enter a start date and an end date, I have a combo box that allows me to select a worker and I have 3 text boxes that I want to populate with the sum of 3 separate columns in another table when the date is entered and the worker selected so the manager can see, at a glance, how many hours each worker has done on the separate items. Maybe I would need an update button at the bottom that, when clicked, would perform the required calculations.
I have 3 queries that return the required information but I need to get one of the columns from the query results put into each of the 3 text boxes. Basically, when I click on the buttons the right query appears, I just want column 3 (which is a sum column) to get put in the corresponding text box and I want all the boxes to display the sum of their corresponding queries when a date and person is selected.The form is called frmSearch, the 3 queries are called qry_sumisshours, qry_sumisshours and qry_sumtcshours. The 3 text boxes I need to get populated from the queries are called txt_ results_ sv_ hours, txt_results_TCS_hours and txt_results_iss_hours.
View 10 Replies
View Related
Feb 24, 2012
I am using workdays to calculate time passed between two dates. I also have a table for Holidays that I don't want counted as work days. This is working well, but I would like my queries to show a null value instead of showing #Error when a date field is empty. Here is my coding in my Module.
Option Compare Database
Option Explicit
Public Function Workdays(ByRef startDate As Date, _
ByRef endDate As Date, _
Optional ByVal strHolidays As String = "Holidays" _
) As Integer
[code]....
View 3 Replies
View Related
Dec 11, 2013
I have a continuous form it has a field (DateStart) and a field (DateStartCarryOver). There is an event on (DateStart) for the (DateStart.Value) to be passed to a new record in (DateStartCarryOver).
My probem is that the new record dosent get saved (i.e. it dosent get an ID so a record isnt created. How can I create that record once the data has bee pased to it?
View 3 Replies
View Related
Jul 8, 2015
I need to calculate the passed working days and show them in a form. I should be also able to use the number in a query later on. in excel I use the formula to get the days passed:
Code:
=IF(ISERROR(MATCH(F10,Dictionary!C:C,0)),NETWORKDAYS(D10, TODAY(), Dictionary!$E$2:$E$43),"Status Excluded")
D = "Date_uploaded" in access table "tbl_All_Cases"
F = "Status_Case" in access table "tbl_All_Cases"
c:c = dictionary case status
E:E = dictionary holidays
"Status Excluded" will show up in the cell if a case has one of the status from the dictionary..I created a table: tbl_Dictionary where there are 2 fields: "Case_Status" and "Holidays".How can I translate the above formula into something that access will understand?
View 14 Replies
View Related
Jan 25, 2015
I'm trying to understand how data is passed using foreign keys.
I'm using Allen Browne's 'Don't use Yes/No fields to store preferences' at: [URL] ....
I've also downloaded his sample DB, RelationBasics, to use as a guide.
Attached is my version of the Student / Sport DB as described on the webpage.
I use 2010 at home (saved as 2003 version) and 2003 at work (JPNSE OS). Both result in the same thing.
The problem I'm having is I cannot get the actual sports to display in the combo-box, only the Sport_ID number.
I've tried building both forms with and without actual data in the TBL_Student & TBL_Sports tables, but no mater what the result is the same.
View 2 Replies
View Related
Sep 22, 2004
hI,
I have a form which has several checkboxes and several textboxes and a command button. the user selects some checkboxes inside the form. when he clicks the command button, i need to run the select query. the select query would be like "select col1,col2,col3 from tablename where check1 = (checkbox1 value) and check2 = (checkbox2 value) and check1 = (checkbox1 value) and so on. ". i have tried this query using VBA. it is working fine. the problem is i have run another query which is very long in characters(like 15 lines involving self join). when i tried to run this query inside VBA i am not able store the query inside a string. Is there a way to come around this problem? I think it would be easy if i can use macros to build this query. please suggest me the ways to do this.
Thanks,
Sridhar!!
View 1 Replies
View Related
Mar 11, 2005
I inherited this database where there is very little code, most of the commands are in macros. I know nothing about macros. I am having a problem with the database. Some of the records are not printing when I print the schedule(this is a scheduling database with dates and job quantities). There is a display checkbox that gets checked when we want a job printed on the schedule. Sometimes it will not print it if we do not have a date for when the files are due or when material is due, so if we put those dates in they will show up on the schedule. Lately though I can't even get it to print some of the jobs if I put in the date when the files and materials are due and when they come in. They just don't want to show up. If anyone could help, I would greatly appreciate it!! Thank you
learnasugo
PS I am working on getting db small enough to paste. Don't know how else to make it so you can see db.
View 9 Replies
View Related
Jul 26, 2005
I am trying to invoke a microsoft Word recorded macro ("Macro1") in the microsoft Access VB code. I am trying to do this after I open the Word application and the file. This is my code so far:
Dim objword As Object
Set objword = CreateObject("Word.Application") '
objword.Visible = True
objword.Documents.Open ("I:FIRST DRAW CHECKLIST.doc")
objword.Run Macro1
The bolded line is the one supposed to tell word to play the recorded macro but after it opens the document, Access gives me a message that says it can't run the macro. Can anyone tell me the correct syntax that will help my code work?
View 2 Replies
View Related
Jan 7, 2015
I'm having a spot of trouble trying to get conditional formatting to work. I have an overview form which displays current quotes going through the system, the QuoteStartTime field is generated from the Now() command.I Would like if possible to show, 1 hour = Green, 2 hours = Amber and 3+ hours Red.
I've tried variations of
hour([QuoteStart])>1
DateAdd("h",1,[QuoteStartTime])
I've done this before with a date only field before, but working with time is taking too much time.
View 1 Replies
View Related
Jun 28, 2005
When you need to run a series of Queries.
And they must be sequential.
Is it better to use a Macro or VBA to call the queries?
Thanks Everyone.
Joe
View 2 Replies
View Related
Oct 4, 2004
I've got a project where an existing access db is being converted for use on a web site.
Our problem is that several of the macros apparently reference an 'S:' drive.
Is there any way I can edit these macros to change / remove the drive reference?
View 1 Replies
View Related
Aug 18, 2004
I have a report that spans 4 years, and for each year it takes about 25 queries to produce the report. The queries for each year are only different in one number in the criteria, so what I'm trying to do, instead of having to manually copy and edit all of those queries every year, is make a macro that will do it instead.
I can make it copy all the queries and rename them, but I can't make them change the criteria in the queries.
View 3 Replies
View Related
Sep 19, 2005
I'm leaving my job of 21 years soon to finish school and hopefully moving on to bigger and better things doing what I like (hopefully something with DBs) and I've got 2 DBs I've built for them. The other day I combined them into one and really started to make sure that there was no need for them to ever get into design mode (not that they could without me -thanks sbaxter).
So while I was testing all the cmd buttons, there's some reports that were made in the early stages that are fed by a parameter query, and are opened by a macro. I've found that the more I'd learned, the less I use macros now, but I did more in the beginning. Anyway, when the input box pops up looking for the parameter, when I hit cancel, I get the "Action Failed" box and then the cancelled by user box. How can I avoid them getting the first error box when they decided they don't want that option?
View 4 Replies
View Related
Jan 17, 2006
I am using Office 2003 on XP. I have a number of reports generated by macros in the database. Once I moved the application to a different pc the macros don't respond, I have set the macro security to low and still no. If I operate the application through the network all works fine but using at the pc no. Any suggestions?
View 2 Replies
View Related
