Debug.Print sSQL
Set conn = CurrentProject.Connection
conn.Execute sSQL
GoTo ThatsIt
ErrorHandler:
Select Case Err.Number
Case -2147217908 'command text not set
Case -2147217865 'cannot find table
Case 3021 'no records
Case Else
MsgBox "Problem with cmdArchive_Click()" & vbCrLf _
& "Error " & Err.Number & ": " & Err.Description
End Select
ThatsIt:
conn.Close
End Sub
I have no problems making the following code work except if there is an apostrophe " ' " in either the txtGivenName or txtSurname . For example O'Keefe it shows an error but not O"Keefe! How do I fix this?
I have to put a ' at the end of each string values
Is there any way to bypass this? I mean I could put a validation code and each time I detect an apostrophe I handle it so that it doesn't create a problem
Hi, I may be dense, but I cannot find an answer to this question anywhere. If it has a simple and obvious answer, my apologies.
I have created an index with a field called "Surname" -- I need to know how to force Access to alphabetize the names disregarding the apostrophes (or a space) - for example, O'Brien. The way it works now, all the O' (s) go at the beginning of the Os (O'Brien, O'Malley, O'Neal, Oakley). I want it to go - Oakley, O'Brien, Oddfellow, O'Malley.
Is there a way to do this and still display the apostrope? I feel that I could set something for the field in the design view, but for the life of me I can't figure it out.
sql = "SELECT Table1.* INTO Table1 IN '" & sExpFileName & "' FROM Table1" CustDB.Execute sql
where the sExpFileName contains the path of The Target MS Access DB The Query works fine. But recently I discovered that if my target File name contains apostrophe It gives error as Query Input must contain at least one table or query
Is there any way to solve this problem. Please suggest me some way
Hey, I have a problem with my application......when the user trying to insert string with apostrophe into txtDesc (text box), the code returns error...
Run-time error '3057'
the database inserts any records excellently, but not with the apostrophe....
This query works and I can spot the record based on 3 fields and display it. Now I have discovered that we have a person who has an apostrophe in his name like O'Brian. The above code gives a syntax error in this case.
I am combining fields one that has apostrophe's and the other does not. exp "if this field is null use data from this field (which does not have an apostrophe) otherwise use current field (which has apostrophe)
"red" blue orange "yellow"
How can I change that in the query without changing the previous query to
I have a Dell keyboard at work and I am trying to find the keycode to the apostrophe/quotation key. I want to disable this when pushed with the ctrl key. I've searched online but could not find anything that shows that specific key. I only see apostrophe keys or quotation keys but not the combination of both which is what I have.
I have a button in one form that opens a different form and want it to show specific data to display. I choose this option in the command button wizard and set it up to have the name from my main form be the same name as my new form to be opened. Everything works perfectly until I choose a name that has an apostrophe in it. So if I choose the name "Ender's Game" it gives me the alert:
"Syntax error (missing operator) in query expression '[bookName]='Ender's Game".
I think I've found where the problem is, I just can't figure out how to fix it. When I looked at the onClick embedded macro it has the Where Condition as:
=="[bookName]= & "'" &[BookName] & "'"
Is there a way to get Access to accept that an apostrophe in the name is meant to be part of the string itself?
I use the follwing code to filter a report based on the listbox selection on a form. Below is the code I use, the problem it will error if the results have an apostrophe in the string.
Private Sub FilterDesc_Click() Dim strWhere As String Dim ctl As Control Dim varItem As Variant 'make sure a selection has been made If Me.ListCarrier.ItemsSelected.Count = 0 Then MsgBox "Must select at least 1 Carrier"
I have a main form [mainform] with several subforms on it, two of which are on a tab.
On the second tab [page79] there is a subform [subformlist] which has like 25 checkboxes on it, any combonation of which can be picked by the user.
I need there to be at least one pick required of this subform, else the record won't show in a general query I have, since the table the subform is based on is linked to the record.
Is there anyway to make making a choice of one of at least one of these checkboxes checked a manditory thing before the user can close or move on to the next record?
I've tried making an "Other" box that on the Table has a default value of "True" which I thought would cause it to show in the query record...to no avail.
The only true way to make this work is to have it mandatory that the user pick from AT LEAST one, usually more before the record is complete.
Any help? Other than writing a MASSIVE If...Then statement...?
Or is there a way to make it so the choice isn't required for the record to show up in the query?
I'd post...but my db is too massive, even zipped. :(
Yesterday I posted a question and rec'd good advice but as usual I don't think I explained myself very well. As the attached picture of my Form shows, I have a checkbox for each Age Group under each Team. I originally had a query that would come up for each Team's particular Age Group and then the required data copuld be input. This leads to too many querys clogging things up. I tried to copy the SQL code of one of the query's to the On_Click event of the checkbox but as I have been informed SQL is different to VB. All I would like to know is how to convert the SQL to VB coding so that I can continue to do the coding for each Checkbox. The SQL code for the first checkbox is:
SELECT tblPlayerRegister.Surname, tblPlayerRegister.[First Name], tblPlayerRegister.Age, tblPlayerRegister.[D'n], tblPlayerRegister.G1, tblPlayerRegister.SP, tblPlayerRegister.Age2, tblPlayerRegister.G1A FROM tblPlayerRegister WHERE (((tblPlayerRegister.Age)<11) AND ((tblPlayerRegister.Club)="Beaconsfield")) ORDER BY tblPlayerRegister.Surname, tblPlayerRegister.[First Name];
If there is an easier way, by all means let me know. Any assistance would be greatfully appreciated.
I have a DB in .mdb format which works perfectly and when all the code is compiled it results in no problems. However. When I convert this to a .mde file, it doesn't work at all and it suggests that there are compile errors where there weren't any before! Please can anyone suggest what the problem might be as I don't particularly want to run this as a .mdb. Thanks
I have got the following query but when it actually inserts the date, the dates will be "08/01/2005". Month and day will be swapped if date is less than 12. I don't know it's happening. It's driving me crazy.
It will insert "8/01/2005" for the first date and "21/08/2005" for the second date. my date format is set to australia and i have set the same in my asp page as well. it is giving the same result even if i try direct from the query.
I have a VERY simple select statement that I am using in a pass-through query: "SELECT * FROM dbo_vReturn;", but I cannot get this to work. Each time I run the query it generates an error: "ODBC call failed - Invalid object name "dbo_vReturn" (#208)".
The dbo_vRteturn is a view in the backend. I can open this table/view from Access using an ODBC connection but when I try to run the pass-through query it falls over!
Does anyone have any idea what is causing this?:confused:
Hi all, this is my first post, I'll try and make it a good one :-)
System: Windows 2000 Office 2000
I have a small network of PC's here which have access to our central database. I have a simple form created using the label wizard which is used to print address labels for mailing lists. On most of the PC's here it works fine but on one or two (which have no obvious difference in thier setup) I get an "ODBC--Call Failed" error and it won't run the report???? I have puzzled over this for a while when I decided that I would remove the Trim function from the lines of the address fields on the label, these are put in automatically by the label wizard. This stopped the error message and the reports now run. I don't know why this is happening but I am hoping someone will.
I have a large application in Access 2003 which comprises a main program file and 4 linked data files which between them contain around 50 tables. The prog file sees the data files through linked tables. Everything works well until I do a compact and repair on the data files then unpredictable problems arise. The compaction is only ever done when all users are off the system and one file at a time. Having done a compaction this week I found that one of the tables appeared corrupt when I browsed it via the program file (the table comes from one of the files which contains other tables working OK after the compaction). The data was garbled and what could be recognised was not in the right place - appears to be moved a couple of columns out of place. There were some fields which reported errors also. However when I browse the table in its native file it is perfectly OK. I can solve the problem by re-running the Linked Table Manager in the program file but that is hard work for 50 tables! This has happened previously when I compacted and I would be better understanding the problem than just fixing it! Can anyone help please?
I now want another query which returns the row which is always the latest STATUSDATE. This is what I have done so far:
SELECT qryFirst.PROBLEM_ID, Last(qryFirst.STATUS_ID) AS LAST_STATUS FROM qryFirst GROUP BY qryFirst.PROBLEM_ID;
However, when I want to add qryFirst.STATUSDATE to the SELECT clause, I get an error „You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)“
I need to see what the STATUSDATE is for each of the records returned in the second query. How do I do this? Please!!!!
I have a database with a series of fields, all set to a String datatype, that I want to be able to fill and then use with MS Word as a mail merge data source. This function lets you query a MS Access db and use the results to fill in predefined spots attached to the various data values in the table. This is great for my needs however a large percentage of times that I have a dash in the value being queried it does not return any results. For example, 01-01 would return no entry found even though I can clearly see it in the database, however it would work fine if I queried for 0101 or any other value in the given field.
Basically I'm trying to figure out why dashes would cause problems. They are obviously fine for being stored in the access database and don't cause any troubles until they are queried for, also retrieving data containing dashes is fine so long as that value is not the one being queried for.
I basically can't avoid the dashes in the data for this setup and as such hopefully can figure out why this happens and how to get around it. Thanks for any help offered.
The ones with stars are "type" tables. All possible IP's are in the tblIP, models in tblModel, office's in tblOffice etc.
Maintenance types are just like "fuser replacement", "toner replacement", "turned on computer" (IT joke)
anyway, On my main form. If I open the form and click the button I made to go to the previous record or next, I am fine.
If I choose an office, or start a record, I get a message that I have to have a record for IP chosen, that it can't leave this new record until it has an IP because of referential integrity.
If I choose an IP and nothing else, I can leave the new record. If I don't choose IP, but choose everything else, I cannot leave the record. AHHH!!!
The thing is, all my combos on the main form are (as explained above) FK's to lookup tables, and all set up the same: all with referential integrity, 1 - many, cascade updates and deletes.
This 1 table is the only one acting this way.
I am including a print screen of my form, as a visual sometimes helps.
If anyone can help me to fix this, I would really appreciate it. I have never had this problem before.
Hey firstly im at the end of the project ive spent months on and thats a MIS datase software. with all the bits and pieces and expert advise ive managed to learn a whole lot of things through this forum.
This is the problem i am having though;
i have a form that has two subforms embedded and since i have to use link tables because i placed the database file over a network somehow the loading seems to take forever, "it actually makes access inactive for about 50 seconds"
i would like to prevent the subforms from loading with the main form until after the main form is up and then i just click a button that makes both of them "appear" and load.
I know my way around Access reasonably well but am by no means an expert. I have created a system that I use in my business for hardware/software requests, and was told to do it in Access. I did ask that we do it on SQL Server with a Web Front End, but we are where we are.
Now, I have been on a SQL query training course so I know the basics, but am a bit confused on this one since Access has been added to the mix.To make matters more frightening, this is Access 2003!
[T_Hardwarelist] ID (primary key, autonumber) Make Model Description
[T_UserHardware] AT Reference ID
[Request] is inner joined to [UserHardware] on the AT Reference column. [UserHardware] is inner joined to [Hardwarelist] on the ID column.
The select query I have is basic and does just what it says on the tin; it shows who has what hardware.The query:
Code: SELECT T_UserHardware.[AT Reference], T_UserHardware.ID, T_HardwareList.Make, T_HardwareList.Model, T_HardwareList.Type, T_HardwareList.Description FROM (T_Requests INNER JOIN T_UserHardware ON T_Requests.[AT Reference] = T_UserHardware.[AT Reference]) INNER JOIN T_HardwareList ON T_UserHardware.ID = T_HardwareList.ID WHERE (((T_UserHardware.[AT Reference])=[Forms]![F_Request]![AT Reference]));
However, when it comes to adding NEW hardware to the requestI have a form called F_Request.I have a sub form called SF_Hardware.The SF_ Hardware subform runs the aforementioned query, and shows what hardware is assigned to the parent request.If I add new hardware via a dropdown on the form, it adds it in to [T_UserHardware], but it also adds another value on the [T_Hardwarelist] table. E.g. if I add a "HP Compaq 8200 Elite" (which is stored in [T_Hardwarelist]) it adds it to the [T_UserHardware] table correctly, but creates a second (third, fourth, fifth) entry on the [T_Hardwarelist] table for the same kit.
I think its confusing because of using a select query and might have to run a 2nd query on save or something like that, I had a working version before that had the make, model etc in both tables and didn't have a 3-way relationship. It'd be nice to have the request table with the high level info, a hardwarelist table with our catalog of kit, and a userhardware table just containing the ID and Reference for the hardware/request rather than duplicating the information.
