I'm trying to pass data from a macro using DDE to Access (it must use DDE), however, I can't seem to get the data to a Module, Macro or Form (either one will do).
Is there a way to pass arguments to any of these three? I'd imagine the easiest would be to run a macro like DDEExecute channel1, "[RunMacro MyMacro]" but how can I pass the data specifically do the Macro?
Hi - I am looking for some help understanding the instructions in the msdn article on how to pass a parameter from a DAP. Please refer to the article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_PassParam.asp
I was able to follow the instructions until Page 2 of this article...where it says: "Add the functions to write, read and delete cookies in a separate global script block."
I do not understand how to create a global Script block. Do I just copy and paste this code right below the 1st code on Page 2? Or, is there a way to create a global script block separately and add this code there?
I am unable to find that much Online resources on DAP as other Access topics. I thought posting my question here would be the last resort for assistance! :confused:
I have a bit of a dilemma in my related tables (screen shot of relationships attached).
Basically at the moment I am using a 1-1 relationship between my tables using the primary key of CYID. The CYID is populated using an auto number at the moment but the problem I have is if the tables become out of sync with the numbering.
What I would like to do is when I create a new record in tblClientDetails is create a new record in each corresponding table that is linked and then pass the CYID across to each table. Can anyone offer any assistance or give me any suggestions for better ways of doing this?
I know there are other threads on this but I have tried all the solutions and none have worked.
I currently have 2 forms, a customer form and a customer health form.
I want to pass the membershipNo from the customer form to a newly opened customer health form.
this is the code I am using but it isnt working, actually it is, but when i delete all the current customer entries and enter new ones it doesnt pick up the details and all the controls on the customer health form are greyed out.
Private Sub cmdCustHealth_Click()
DoCmd.OpenForm "CustomerHealth", , , "[MembershipNo] = " & Forms!CustomerForm!membershipNo, , acDialog
End Sub
The control source on the text box on the customer health form is "membershipNo" where am I Going wrong?
Hi,
i'm quite new to Access and I have a problem passing data from one form onto another.
I have a form that I use for quotations based on the table T Quotations on it I have:
QuotationID as my primary
RefCust referring to which customer
Date
and a few other fields
Now i'd like to add a button that when clicked will open my order form. In other words when a quote is confirmed I'd like to click on the quotation form to confirm it and create an order on the order form.
Order form will open with a orderID autogenerated BUT will return the same RefCust as on the Quotation form, the same date and will give the quotationID as Refquote onto the new form.
Ex: on form quotation I have
Cust: abc company
QuotationID: 123
Date: Feb 25 2007
Quote: $300
when i click on confirm
the Order form will open with:
OrderID: 002 (autogenerated)
RefQuoteID: 123
Date: Feb 25 2007 (even though today might be April 10)
RefQuote: $300
Please help me
I have a hidden field on my form called "key". I can reffer to it as [forms]![MyForm]!key Is that right?
I also have a subform on this form that uses an sql query. I want this query to match on the key from above.
Shouldn't this work? select field1, field2 from table1 where keyvalue = [forms]![MyForm]!key
It doesn't. It returns a blank record.
Any help would be greatly appreciated.
I have a form called frmSchedule. Within this form, I have a combo box called cmbCD. I have code in the cmbCD NotInList Event. If the user inputs a number not in the list, the folowing code opens up a new form (frmPatients). The user can now add a new record to the database.
Code:
Private Sub cmbCD_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String
CR = Chr$(13)
[Code] ....
I got this code and it works. However, when the user inputs a new number in frmSchedule, the NotInList event takes them to frmPatients. The users then have to re-type the number in frmPatients.
My question is: Is there a way I can trasfer the NewData from frmSchedule into frmPatients, so the users don't have to re-type the number?
I have a button on a form that creates an email and inserts fields from my database. I have a field that is set to Long Date format type, but when it populates the email, it shows as a Short Date format type. Is there anyway to retain the Long Date format?
View 1 Replies View RelatedI am preparing a database application that will be used by one person in an organization. Frequently , (every year or two) the program will be given to another (usually a laptop) user in the organization as that person assumes the task of the program’s operation and responsibilities.
I would like the user to load the program from the installation CD (made with Wise Installation System and SageKey softwares) on each computer each time there is a brand new user and each time a subsequent user takes over. However, each successive user needs to have the data files passed from the last user.
At face value it seems that having a front end/back end arrangement might be the most desirable. Can anyone jump start me on the best way to accomplish this process to EZ data transfer?
Thanks in advance to all,
JQ
I have a search form that searches for student ID from the student demographics table. I have it set when I click on the student ID (Frm_Student_Demographics_DailyCares) it will open the student visit form. When the student visit form opens it will show the student ID on the Student Visit Form but it is not being recorded in the student visit table.
I have Student ID control source on the Student Visit Form set as:
=[Forms]![frm_SearchStudent_DailyCares]![Frm_Student_Demographics_DailyCares].[Form].[SD_Record_ID].[Value]
My tables are called Student Demographics(parent table) and Student Daily Cares(child table) and they are linked by Student ID.
I have done this before on another part of this database but I just simply can't seem to find what I missed.
Here is a sample of my forms and tables.
How can I pass data (an employee's first and last name) to a report ? I captured the employee's name from the listbox, but can't seem to pass it to the report. The desired report will only have the employee's name and records for related fields on the report. The table (contains emloyees' history data), form name, listBox (contains employees' names), and variable (contains the employee's name) are listed below.
Table_Employee_Detail_History
Form_Employee's Reports
Report_Attendance_Report
stremployee (variable
I have a function in a module that looks like this (it takes 2 dates as arguments):
Code:
Function get_KPIScanAgeRange(in_ScanDate As Date, KPIDate As Date) As String
Dim ret As String
ret = "Invalid"
' return value, by default is because age is negative number
[Code] .....
I'm trying to pass the date in a text box which exists in a form called "d3FormAging". The text box name is "KPIDate". The query looks like this:
Code:
SELECT Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate]) AS KPIScanAgeRange, Count(Query_d3_Open.[Scan date]) AS Scans
FROM Query_d3_Open
GROUP BY Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate])
ORDER BY Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate]);
For some reason Access doesn't recognize the "[Forms]![d3FormAging]![KPIDate]" when I pass it to the function. I get run-time error 3070 with information that Access can't recognize this expression even though the code should be correct as it's made with the expression builder and I'm sure the form that contains the text box is open when the query runs.
I have a function that when called transfers a query recordset to an excel spreadsheet then emails it. At the end of the function I use code to write the date sent to a table. Each time the function is called I only need records in the query that have been modified since the last time the function was called. I have a field in the query 'LastModified' with a criteria '>[Enter Date]'. I then look up the date in the table and enter it manually. I know how to look up the last date sent in table using code but getting the >#SomeDate# in the query with VBA.
Code:
DoCmd.TransferSpreadsheet acExport, , "qryUpdateWebmaster", _
"C:SubmarinersUpdates_Sent" & fname & ".xlsx", True, "Webmaster_Update"
I have a form with a date field, when the user creates a new record, I would like the date field to automatically fill with the most up-to-date date from the Orderdate table.
Basically I need the code to do the following when a new record is created;
Search the Orderdate table for the most recent date and then auto fill the date field on the form with that date!!!!
My thinking so far...
Private Sub Command34 - where would you set this event on the properties i.e. Before update or On Got Focus ?
Docmd.OpenQuery "QryFindMaxDate"
I'm not really sure how to pass the date to the text box on the form,
End Sub
I have a form that displays a stock inventory, one of the fields I use is called [ItemCode].
On the Stock_Inventory form I have 10 command buttons that open other forms to display additional product information.
These were previously sub_forms that sat in a tabbed control box within the Stock_Inventory Form but as it now takes a while for the form to load (lots of queries running each time you search for a product) users are getting slightly frustrated.
So, what I decided to do, was move each subform from the tabbed control into a new form and open that form when it is needed by the user.
OK, when I open Stock_Inventory and search for a product [ItemCode = "ABC123"] and then want to see the Sales_History, I have to open my new form, Stock_Inventory_Sales_History, within this form are two sub-forms, each subform has a field called [ItemCode].
However, when Stock_Inventory_Sales_History opens it asks me to input the parameter [ItemCode] twice, I presume this is because the Stock_Inventory_Sales_History does not have a record source and that the subforms need [ItemCode="ABC123"] to run each query and return the data.
I need to pass the [ItemCode] from the Stock_Inventory form to each of the 2 Subforms that are on the Stock_Inventory_Sales_History form automatically.
I'm trying to pass values from a combo box to the criteria for a select query. The first 2 columns of the lstCompany combo box are Serial Number and Company Name. I have tried a number of different ways to no avail. Anyone know?
Thanks,
Beagles
Hey folks,
Not sure if this is the best forum but decided to put it here anyway.
I have a Access query that reads:
SELECT bleh
FROM blah
WHERE something > 10
Then in Excel I pull the data across using:
Set qdf = db.QueryDefs(qryName)
Set rs = qdf.OpenRecordset
and paste the data using
ws1.Range("IV1").End(xlToLeft).Offset(0, 2).CopyFromRecordset rs
I now want to change the query so the where statement reads
WHERE something > [amt]
So the question is how do I pass the value for the parameter value for [amt] from excel to access?
I have a form design where to enter a new "system" (what we call a new group of members) you have to specify the representative (the member that will represent the system).
If the system is new AND the representative is new, then both need to be entered. Typically this will be done through the "New System" form that has a combo box for you to choose the representative (but the representative won't be there!). If the representative is new as well, then the user clicks "Add New Rep" which is located next to the combo box.
This is where things get tricky, I'd like to use only 1 form to load a new rep. My idea was to use an openarg on the docmd.openform method that specifies what the control should be.
Code:
DoCmd.OpenForm "RepresentativeAddNew_Form", , , , , , "System_ID|" & Me.System_ID
That is the code that is called when the user click "Add new rep" and in the on load event I have this code:
Code:
'Use this version if the ID is text
Dim x As Variant
Dim strControl As String
Dim strID As Long
'If parameters exist, use them
[Code] ....
My goal was to pass the value to the combobox and turn it off, that way they can't change anything about it. This was nice because if the openargs is null (there were none passed) then the combo box would be enabled and the user could select the school that way.
But this doesn't work, for some reason, the combo box has NOTHING done to it (I have the control name correct).
Is this due to the way comboboxes work? Or am I missing something?
I should point out that the on-load event was not my idea, I found that online but makes perfect sense. I was trying something similar but was trying to use global variables because I wasn't aware that OpenArgs was a valid control!
I would like the user to be able to select the months he wishes in case they want to look at calender year, financial year or just a custom group of months. If I use the wizard and pick dates say the start and end of the year I get the following code in the row source of the chart control:
Code:
SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] WHERE ([DatePaid] BETWEEN #01/01/12# AND #31/12/13#) GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));
So I decided all I needed to do was replace the dates in the above code with my own global varible which i would pass custom dates into via a form. Which I called getds() and getde()
Code:
SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] WHERE ([DatePaid] BETWEEN >=#getds()# And <=#getde()# ) GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));
I am trying to pass parameters to my qury thru my combo selection. I keep getting this error "Data type mismatch criteria expression", does anyone have an idea why?
WHERE (((fShiftWorked([tblTimeLog].[timeStart])=[Forms]![frmOperatorWorkDone]![cboShift] Or IsNull([Forms]![frmOperatorWorkDone]![cboShift]))=True));
I have spent so much time onthis already and i am sick of it :mad:
Attached is my db. Please help me out here.
I'm having a rough time trying to figure out how to pass a date to an SQL statement that Excel VBA macro will run. The date is in a cell (A1) formatted as 'm/d/yyyy'. Let's say it's 2/1/2014. I want to run an SQL statement that retrieves data from a table where a field is greater than 'A1'. The table field is a date/time field and has values formatted as 'mm/dd/yyyy'.
I've tried various syntax on the Where but cannot get it to work.
sd = Range("A1")
SELECT [tn].[Date Submitted]
FROM[tn]
WHERE tn.[Date Submitted] > """ & sd & """
This results in the following where clause that does not work.
WHERE tn.[Date Submitted] > "2/1/2014"
I'm running a VBA routine in Excel that loops through a lot of data. As part of the process, I'd like to pass a variable from Excel to an Access database that is open and have it run a query based on that value.
View 4 Replies View RelatedHi All,
I'm not sure if this should go in here or in VB Programming, because it's to do with an Access Database but it's VBA code.
But I understand that VBA and VB are different in the way they work.
I think this may have been looked at before, but I'm not very good at using the search function. I've looked on google, but all I've seen doesn't seem to be for my use, so I came here as recommended by "RadioActive Frog" who I believe is a member here.
Right, basically, I am writing an Access database for my company's ordering system.
I have a form called "Enquiry" which has many tabs, one of these is the "client" tab in in here are many fields:
Firstname
Surname
Company
Addressline1
Addressline2
Town
County
Postcode
Phones
Fax
AltMobile
Email
ContactType
Now, there is an option to enter a new client or search the database for an existing client and it is basically the "new client" which I'm having issue with at the moment.
Basically, clicking the button "New Client" opens another form called "Client". Here, the user enters the data into fields which are the same as above (but on this contact form).
When done, they click a button called "Save and Close" which then asks the user if they want to paste their entered data into the original main "Enquiry" form.
It's this last bit, the transferring of this data I can't get my head around and would be incredibly greatful for help with.
My Code is below:
Code:Private Sub Save_Click()On Error GoTo Err_Save_Click ' On clicking save, a dialogue box will open asking if you want to paste this data ' into the enquiry form. Clicking yes will do this. Clicking no will just close the box 'declare intpress as an integer Dim SavePress As Integer 'Save Command DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 'when save and paste is clicked, ask if the information should be pasted into the form or not SavePress = MsgBox("Would you like to paste this Contact Information into the Enquiry Form?", vbQuestion + vbYesNo, "Paste details") If SavePress = 6 Then 'If the enquiry form was the form which initiated the cotact form, then copy and paste 'the informtion into the enquiry form and the close the form 'If "Enquiry" = Me.OpenArgs Then <------ I have decided not to use this, as it doesn't seem to work. (I'm probably not using it correctly) Enquiry!FIRSTNAME = Me.FIRSTNAME Enquiry!SURNAME = Me.SURNAME Enquiry!COMPANY = Me.COMPANY Enquiry!CATEGORY = Me.CATEGORY Enquiry!ADDRESSLINE1 = Me.ADDRESSLINE1 Enquiry!ADDRESSLINE2 = Me.ADDRESSLINE2 Enquiry!TOWN = Me.TOWN Enquiry!COUNTY = Me.COUNTY Enquiry!POSTCODE = Me.POSTCODE Enquiry!PHONES = Me.PHONES Enquiry!ALTMOBILE = Me.ALTMOBILE Enquiry!EMAIL = Me.EMAIL DoCmd.Close acForm, "Contact" Else DoCmd.Close acForm, "Contact" End If Exit_Save_Click: Exit Sub
But It's not working. It's giving an error saying "Compile Error: Variable not defined" and it highlights the word which I've mad RED in the code above. Now, I tried changing the exclamation for a fullstop, and I also tried writing "Form.Enquiry.FIRSTNAME" (and also with exclamation marks). None have given a working result.
I'll try and get some print screens too for more visibleness (new made-up word there) so you can see what I mean.
Blessings,
Si
Edit: here are a couple of print screens:
1. The Client tab/page of the main enquiry form:
http://i6.photobucket.com/albums/y218/Mr_Si/Enquiry.jpg
2. The Client Details form, which opens as a result of pressing the "New Client" button in the main enquiry form (shown in the background):
http://i6.photobucket.com/albums/y218/Mr_Si/Client.jpg
Argh! I forgot I couldn't post links to URLs
I am using Excel/VBA as a frontend and Access backend. The sheet2 stores the queue name and Queue number. We have to update the sheet1 from column L to column O by looking for the values from the Access table for the date selected from the comboboxes. Now In sheet 2 , it says Queue number and in actual in access table it is the combination of Type & Type1 & Type2. So we have to look for Type & Type1 & Type2 in the table and find out total Batches ,Total Envelopes,Total documents and total pages and then store the values in the ExcelSheet1 from column L to column O.
The following formulas will be used in the select statment:
Total Batches = count(BatchNo) for date selected
Total Envelopes=sum(Envelopes) for date selected
Total Documents=sum(Cases) for date selected
Total Pages=sum(Pages) for date selected
I am trying to set up some data access pages as data entry into a table with access 2000. I can see the records in both the data access forms and the HTML forms, but cannot create new records into the table. I have tried to change the property to DataEntry etc... but nothing seems to work.
View 2 Replies View RelatedI have a statemtn like this
DoCmd.OpenQuery("UPD_RECEIPT_IMP")
When I run the above, it pops up the input box for user to key in the argument.
How can I stop this and pass the argument to the query in macro
Need help on this.Thanks...