Access 2010. I have a query that I referenced it to a combo box in a form.
If I rename the combo box, save it, and then rename it back to the same name it works fine for a while and then stops working again.
I have a query that I referenced it to a combo box in a form. In my form I created a combo box which has a list of VendorName and I want my query to opens a VendorName report based on whatever chooses in the combo box so that I use the combo box name in the criteria of the VendorName in my query. Somehow I get an error which says "The Microsoft Office Access database does not organize "[Forms]![Form1]![Vendor_Name]" as a valid field name or expression".
View 10 Replies View RelatedI maintain a grade book application that uses many queries whose results are determined by "school year". Most of these are reports and I have a combo box on the Print form for that allows the user to select the school year. The criteria field of the several queries derived by school year is:
[Forms]![Main Navigation]![Print Form]![SchoolYear]
SchoolYear being the combo box control.
They work fine.
I have now added a function to export data to Excel. This is done in a VBA module and I am using a query to select data for the record set I use to write to Excel:
Set objRst = Application.CurrentDb.OpenRecordset(strQueryName)
When I hard code the school year in the query criteria field (i.e. "2012-2013") the process works fine, but if I revert the query to point to the print form field as above, I get an empty recordset.
The Excel export is executed from a control on the Print Form, so the form is open and the combo has data showing, just as it is when a report is run whose data is derived from a query.
When I execute the query from the VBA module, the query is not getting the school year selected on the Print form passed to it properly.
I have created a form and changed it's pop property to Yes and named the for to 'rtchange' however vba code editor is not recognizing this form in any code i try and reference it. it's giving an error variable not defined??
View 4 Replies View RelatedI have a form that has a combo box (box1), Its row source is from a table (table1) than the forms record source (query1). The data box1 sources is from column1 in table1.
I then have a text box (box2). I need it so when i select a record in box1 (from column1) that it displays the corresponding data in that same record (and table) but from column2
Example:
A table has a column with equations and another column with the answer.
I select "1+1" in the combo box
Then in the text box it displays "2"
Is there a way for a SQL QueryDef object in Access to look up the name of itself in the SQL and return that as a value in the result set? Ex:
Code:
SELECT [foo],
[bar],
[something]
@NameOfQueryDef
FROM [table]
How to reference current row in SQL string? I have a qst while using RowSource. I have 3 tables:
[1] CITY
col: _value _name
[2] STREET
col: _city _value _name
[3] ADDRESS
cal: city street
The problem ocurred when i designed ADDRESS table:
The RowSource of [city] is "SELECT _name, _value FROM CITY".
And I set the binding-column as 2, so that _name can be displayed and the real value is _value(integer).
But here comes the trouble: how can i set the RowSource of [street]?
The _value of STREET table depends on _city, and _city is _value in CITY table.
E.g. _name of STREET is "Wall Street" when (_city,_value) = (1,1),
But "Lincoln Avenue" when (_city,_value) = (2,1)
I want to use "SELECT _name, _value FROM STREET WHERE _city=CurrentRow.city" as RowSource, but what can replace the "CurrentRow"?
I created a query that pulls information from 2 other queries and everything was going fine until I saved the query. I now get a circular reference error
SELECT [CashValue Link Query1].Facility,
[CashValue Link Query1].[Financial Class],
[CashValue Link Query1].Date,
[CashValue Link Query1].Date,
[CashValue Link Query1].[Total A/R],
[CashValue Link Query1].Current,
[code]....
Within a query, I'd like to reference another query field based on a date specified as a parameter.
In my query, there are fields for each month: [January],[February], etc.
I have a field titled [Current Month], based on the parameter [As Of Date]. So if when running the query, the parameter pops up and I type 5/6/2013, it knows that the month is May. I know how to return May in the current month field (format([As Of Date],"MMMM"). But how to I return the value that is in the May column?
I have a query which gets information from 2x tables where the I'd on one table is the reference number on the second table.I would like to know how I can remove the duplicates on my reference number field?
View 3 Replies View RelatedAbout three weeks ago, my work computer crash hardcore. Windows (2000 ver) had many corrupt files in the Windows Directory and in the Registry. Because of that, IT gave me an up to date computer running Widows XP. I am still running the same version of Office (2003 Pro).
I think because I have a new machine and a different platform, I am now have issues with trying to administer the permission rights. MS Access is not recognizing me as Admin and/or Creator of the db, therefore I am not able to modify permission rights....... :eek:
Does anyone know how make this db recognize that I am the Creator of it?
Thank you in advance for your help.:cool:
Kilch
I have a table I'm trying to query information out of. Key fields are below:
RecNo (Key)
ParentRecNo
Description
I need to have a "record" in the query show both the description of itself, as well as it's parent. I was hoping to use Dlookup directly in the query design. If there is no other way, I suppose I could create a temp table and look through the recordset in VBA and dump them into the temp table, but like I said; quick and dirty was the hope here, it's for my own use, and the tables aren't large 50-100 records is what I'll be pulling out when I use this.
I want to populate a table with field:date and field ref nr.(7 fixed ProductID's)
But only weekday and to 3 years from now. (every weekday)
Example
field:ID | date | fixed.ref.nr
--------------------------------
1 | 7/4/2015 | 1001
2 | 8/4/2015 | 1002
3 | 9/4/2015 | 1003
4 | 10/4/2015 | 1004
5 | 13/4/2015 | 1005
6 | 14/4/2015 | 1006
7 | 15/4/2015 | 1007
8 | 16/4/2015 | 1001
9 | 17/4/2015 | 1002
10 | 18/4/2015 | 1003
.... (ID field is autonumber field and not important..)
How to do this with a append query or code?
I have a replica database in Access 2000.
I changed a form in the design master that had nothing to do with the date or a date field. Everything appeared fine.
Now I updated the replicas, and an error is happening on all replicas as well as the design master. Any reference to Date() or Now() causes errors.
If it's a field on a form it has an #Name error. If Date() or Now() is on a report, a popup to enter parameters appear for the date. This database with these codes have been working for years.
Is it safe to say it's no longer recognizing Date() or Now() ?
Any ideas on a fix?
I'm experiencing an interesting problem with a database I have been working on. For some reason, when I tried to enforce referential integrity between two fields, whose values should have matched perfectly, I got an error that I couldn't because they didn't match. When I ran an unmatched query, I found that the numbers DID match, but for some reason Access wasn't recognizing it. Even when I type the values into the field to "correct" it, it doesn't recognize it; I have to copy and paste from the master table into the child table in order for the values to be recognized as matching.
The same thing happens when I try to filter or query the table; if I type the value, it says no records are found, when I copy and paste, or select from the combo box, it works.
ANY IDEAS?? this is really weird and it's happening on multiple computers so it's not just one system. And, this has never happened before with this file or any other that I've worked on...
Hi !
I use access 2000 (french) and runtime access 2000 (english) on XP.
After upgrading workstations with last microsoft security patchs, the use of left, mid or right functions in queries doesn't work anymore.
We know now that the patch acted on MDAC, is it the answer?
I don't know how to make it work.
If you have any idea, it would be great.
LawraC
I am using this formula to format my date field
Convert this:A601030730 to 03-Jan-06
A6 = Year
01 = Month
03 = Day
0730 = Time (not worried about that part)
ApptDate: IIf([abap_030] Is Null,"",Format(DateSerial(Val(Mid(Left([abap_030],6),2,1)),Val(Mid(Left([abap_030],6),3,2)),Val(Right(Left([abap_030],6),2))),"dd-mmm-yy"))
I get the date to "Look" the way I want, but Access does not seem to recognize the formatting as it will not sort properly?
Any suggestion would be welcome, thanks
Ziggy
I have a form with a query set up as the record source. If there are records in this query, the form will display. When the user clicks a button, if there is another record in this query, the form displays the next record. When it gets to the EOF, it should close this form, but EOF is not being recognized (I run debug and it says it's false when the button is clicked)? I get run-time error '3201' after I click the button twice.
I know for a fact that there are 2 records in this query. So once I click the button on the 2nd record, the form should close.
Code:
Private Sub SelectTblMyMedButton_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("qryMedDataSelect", dbOpenDynaset)
[Code] .....
Basically what I have is a database for tracking/logging parcels that arrive to the office. I want to be able to generate a reference number based on the date of arrival: i.e. the reference number should be ddmmyy### where ### is a sequential number. I know that I could just use the primary key's autonumber, for the sequential number but if I do this then the sequence will not restart at 1 on each date and because we receive a lot of parcels the reference number will grow to be too big to print out on the collection slips in just a few months.
two tables (one with the date and staff on duty that day and the other with the parcel's info') with a one to many relationship
I also have a query (Named: FullLog) that picks up the following data from the tables:
Name - Description - Size - TrackingInfo' - Staff - DateReceived - Count
The field named Count is a DCount function that I used to find out the number of times each date is repeated. This is the Expression that I used:
Count: DCount("*","FullLog","DateReceived = " & [DateReceived]) [Note that DateReceived is first converted into a string using CStr()]
This is as far as I have been able to get, I have been looking for weeks for a solution to this problem but I have yet to find one. I don't even know if the DCount function is the correct way of doing it, I did read somewhere that this produces a very slow query.
Effectively what I want to be able to get is something of that resembles the following
DateReceived - ReferenceNo
051013 051013001
051013 051013002
051013 051013003
061013 061013001
061013 061013002
071013 071013001
071013 071013002
071013 071013003
071013 071013004
071013 071013005
081013 081013001
081013 081013002
091013 091013001
101013 101013001
I created a db in Access 2010 and users get an error of db format not recognized.
View 9 Replies View Relatedhi
how do i reference a field on the parent form from the child form e.g.
i have a button on the child form which needs to read a value on the parent form and use it i na calculation etc
thanks
I'm using Access 2003 with a frontend/backend database on a network. Several of our forms have image controls with links to a folder of JPG images, and these have worked fine for months. I have followed the recommended linking method described here (http://www.access-programmers.co.uk/forums/showthread.php?t=92837&highlight=jpg+link) and as I mentioned everything has worked fine until recently. Foolish me decided to try out Office 2007 beta for a few days but uninstalled it after a few days. Now, on my computer only back on Access 2003, whenever I try to open one of the forms that previously worked I get an error message saying "Access does not support the format of the file '....jpg. Try converting the file to BMP or GIF format". Copies of the same frontend works fine on other work stations, and I can open jpgs in Word and Excel. Or, to be even more frustrating, an embedded jpg will show up fine in my copy of the program - it's only linked jpgs that are rejected. I first repaired Office 2003, then completed unintalled and reinstalled it - same error message. 2 hours with MS tech support has been likewise futile. I'm guessing it has to be some kind of registry error, but nothing I seem to do makes a difference. Any suggestions at all will be greatly appreciated, because my next step is a complete format and clean install of everything.
View 2 Replies View RelatedHi,
I'm Italian and I started playing (I don't work on it) with access to create an Airline Simulator for MS Flight Simulator. My "program" read data imported in an Access table by a secondary program, and work on it. :cool:
One or Two of my Beta testers found this problems:
1) function "date()" is not recognized in Access 2003 and 2007. in 2003 is read as "date"; in access 2007 is read "date(". The one with Access 2007 resolved the problem reinstalling the Office Suite 3 times. The third was working. nothing else changed. :mad:
2) Access request the OFFOWC.DLL, in an Access2003 version. But only one user... and that .dll is from Access 2007!! :confused:
I wrote the database in Access 2007 and in Access 2003, the file is obviosly Access 2003 format.
I attach it here, for someone that wanna try if he has the same problem. PS due to size of the file, it's a zip containing a .rar file, that contains the .mdb (zip only was 660 Kb)
Do you have some indications!? Thanks a LOT!! :)
Lorenzo.
I use the following code to achieve following
a) Open a word document from MS access / VBA,
b) Connect to data from a query
c) mailmerge it
d) Save it as a new document containing mailemerged letters.
The code does open the required document as I can see it open.
I put a message box to recheck that filename/path is what I mean, and to to know till which line the code runs. Code runs till that point and that word doc is seen open. I get the error message before
Then I get error message.
I was fooling around and was getting message " This command is not available because no document is open" I tried to remove few lines thinking redundancy. Before getting the message, I was also getting the error trapping message "No documents opened."
Now I am getting different message.
I am getting message " The remote Server Machine does not Exist or is unavailable"
Following is the code I have written.
Dim xlApp As Object
Set xlApp = CreateObject("word.Application")
xlApp.Visible = True
fn = FormPathName("941 2014 AutoOpen.doc")
[Code] .....
I have re-written this question as I think I managed to cause confusion!
(I really do need help!) My problem involves three tables. The first table is NAMES with an auto-number key field called NamesID.
The second table is a list of Dance Classes, called CLASSES again with an auto-number key field called ClassesID.
The third table is a list of MEMBERS in each dance class.
Obviously, the MEMBERS can belong to many CLASSES, and each of the CLASSES can consist of many MEMBERS.
My aim is to add a new record in MEMBERS by using a ComboBox containing records from the NAMES table.
On the form, I have a combo-box which shows me the list of CLASSES
(ComboClasses). I click on the class.
I then click on the NAMES combo-box, click on a name and want it to appear in the table of MEMBERS for the class shown in the CLASSES combo-box.
The problem
I am selecting the name using “ Select * From NAMES where Names.NamesID=[Forms]![FormName]![ComboNames] (supplied by supersubra) but how do I get the ClassesID into MEMBERS record.
At the moment, if I add a name that member appears in every class, or I have to manually insert the ClassID.
Philip
How to get a third combo box on a form to be optional.
I have a form with two combo boxes which filter out the results on a query.
However I want the third combo box to be optional i.e. if it is blank then just the results of the first two combo boxes are shown. If the user selects all combo boxes then obviously the results are based on all combo box selections.