I do a query to remove "." from a list of my servers. This works great, but for every name on the list without a "." I get an error in the output. Is there any way to remove these errors from the output, so I only see the data that has been properly pruned down?
here is the query i'm running
sort: Left([AgentName],InStr([AgentName],".")-1)
This is, I think a very easy question: What would be the apropriate form event to use when leaving a record on a single form (When you press next record for example)
View 3 Replies View RelatedI made a Table with one record per person which is entered initially and contains a sub-table which is a list of the drugs they are taking. As people are notoriously bad at spelling drugs they select them from an alphabetacised drop down list held in another table. The idea being that they check the spelling before entering them on the main table for any new drugs. The subjects each come back and have a number of measurements taken on a number of visits.
So the main table and the drugs table have a one to many relationship and the main table and the visits. In both cases the tables are linked by the same unique identifier which is compulsory and unique in the master table. The data is input using a master form and sub-forms.
I havent done any of the input but when I looked at the two sub-tables they each have a number of records which are blank in the identifier feild.
How has this happened? How do I stop it happening and is there any way of finding out which record these visit data and drugs should belong to?
Hey guy im kinda new to this program so excuse me if my question is simplistic:I built a form for making Purchase orders. In the main form...a user enters a bunch of text/num info (name, address, ect)into the table. After that i have it tab to a subform..where a user can add items by line (as many as they like). All good till here: Once i go to click out of the subform...i get message "You cannot add or change a record b/c a related record is required in table 'UCT_Req'"My relationship has a 'one-to-many' link (the req number to the many line items). I have auto-numbers counting up in each table.My main problem seems to be attaching the entered line items to a specific record in the main Purchase order form...and then opening a fresh form afterwards. Any hints?Thanks folksSTeve
View 2 Replies View RelatedI am trying set up some criteria for a create table query. I am using multiple fields from different tables, I am trying to delete certain feilds if they meet the criteria. I am using iif([letter c_last]=[letter a_last],null,[letter c_last]) in the criteria for letter c last, but when I run it a box appears to enter criteria for the last name field? I want it to look at the field not for data to be entered manually. I hope this makes sense. Any help would be greatly appreciated.
View 1 Replies View RelatedI'm pretty new to Access, so please overlook any stupid questions :)
I have a data entry form that has one field that cannot be a duplicate. So, in the table I have it Indexed with No duplicates.
The problem is that the form doesn't notify the user that they have entered duplicate information until they close the form. So the 10 minutes they spent filling it out is wasted.
So, after consulting various posts, I did a BeforeUpdate event with the following code:
(the field that must be unique is called "LS Number", in the form "Q and D" based on the table "Q and D Database")
Private Sub LS_Number_BeforeUpdate(Cancel As Integer)
On Error GoTo LS_Number_BeforeUpdate_Err
If (DLookup("[LS Number]", "Q and D Database", "[LS Number]=Forms![Q and D]![LS Number]")) Then
MsgBox "The LS Number you entered already exists. Enter a unique LS Number", vbInformation, "Duplicate LS Number"
End If
LS_Number_BeforeUpdate_Exit:
Exit Sub
LS_Number_BeforeUpdate_Err:
MsgBox Error$
Resume LS_Number_BeforeUpdate_Exit
End Sub
When I test this by entering a duplicate LS Number, it gives me the error "Type Mismatch." Unfortunately, I have no idea what this means, nor do I know how to fix it.
Please help, my head hurts!
I have a database where I have a number from a client. We copy/paste it from a pdf file. The number has dots in it (1.00.00.000). I would like to create a solution that when I paste the number in the field and go to the next field, it will take out the dots from the number.
View 3 Replies View RelatedI have a form for rentals where I have fields for car details and for client details. I have a list box for clients so that when I enter the Client Id I automatically get all the records filled out on the form, from the clients table...
This works but the problem is that sometimes I have a new client who rents a car that is not on the database so I insert the client details manually on the rentals form without filling in the client ID as this client does not exist on the database.
The problem comes when I want to save the form it wont allow me to leave the client ID field blank... I imagine this is because of the relationship...
I have a form that allows users to search the database for records based on there criteria. The form allows the user to search, through combo boxes, by variables. The first is MAT, the second is Relocation Area. The search works fine when the user selects options form the combo boxes. However sometimes it is neccesary to leave the location field blank and only search by MAT. When the location combo box is left blank no records are found. Is there anyway around this. The SQL code for the query is below:
SELECT [Extract Data].[ID], [Extract Data].AREA, [Extract Data].[MAT], [Extract Data].[Relocation Area 1], [Extract Data].[Relocation Area 2], [Extract Data].[Relocation Area 3], [Extract Data].[Grade Score]
FROM [Extract Data]
WHERE ((([Extract Data].[MAT])>forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 1])=Forms![Data Search]!SearchArea)) Or ((([Extract Data].[MAT])>Forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 2])=Forms![Data Search]!SearchArea)) Or ((([Extract Data].[MAT])>>Forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 3])=Forms![Data Search]!SearchArea));
Any help would be much appreciated
Nick
I have some code that opens and populates an Excel spreadsheets then does a save as to a specific directory and also outputs a PDF. This code is working with one small problem, I am leaving the first instance of Excel open and can't figure out where I should put the code for it to quit. Here is my code:
Code:
' This code creates a recordset based on the current reservation in order to gather data on
' a specific reservation, to output to a payment schedule excel spreadsheet which calculates
' Payment dates and amounts for specific reservations
' Declare variables for recordset
Dim rst As DAO.Recordset
Dim db As DAO.Database
[Code] ....
The instance that I need to close is shown in red.
Hi
Bit of an Access beginner and am trying to sort something out for work - not sure why they've asked me!
I've created a query to search on a couple of items using drop down boxes on a search form I created. This bit of it works fine, I used this site http://www.fontstuff.com/access/acctut08.htm and copied what he had done. This is fine.
I now want to add a date search to the same query. I know I can use Between [..] AND [..] but if I leave the boxes blank it finds no records. I'd like it to search and include all.
Ideally I want to include 2 extra text boxes on my form that I can put a to and from date in (or not put a date in and it find everything).
Hope that makes sense, please can someone do me an idiots guide?
Many thanks
Phil
how to stop user from leaving field without selecting item or entering data
View 2 Replies View RelatedI have an application that enters candidates in, who apply for a job.
I found that I can enter a candidate in, and if I dont enter at least an address type or phone type ( giving that candidate an address record, albiet a blank one (just a type of address) and a phone record) then the candidate wont have a sub record for addres or phone at all.
This is hurting my reports. They are set to pull the primary address and phone only, as we dont need to have 2-3 contact information for a candidate on a report This is how I found out that I had blank address and phone for some test records. I need to be able to check when a person does a number of things, that at least the combos for phone type and address type have been chosen as something. This will ensure that they have a primary address and phone, as I have code that takes care of all that. I tried putting the following code in the form's before update, but since it is calling to a subform, the minute I tab TO the address combo, I get the message. The form is updating before I get the chance to skip the field.
Here is the lay out. A user can enter in candidate, their address, phone, application and activities. Address, phone, applications and activities are all sub reports on a tab control. The user should be able to move freely within any record, and only be bothered when they try to leave, if important information is still empty. I dont want to control how they enter ('you must enter this first!' lol) just that they do enter it in the end.
What I want to do is check when the user is leaving a record.(closing form, going to another record, clicking the search button that opens another form etc) if either combo is empty (no primary contact info) then it gives the message and goes back to the combo that is empty.
Here is the code I have. If anyone can help me find the right place to put it, I would really appreciate it. Thank you,
If IsNull(Me.[subAddressEntry].Form![cmbAddressTypeID]) Then
msgbox "You must enter an Address"
Me.[subAddressEntry].Form![cmbAddressTypeID].SetFocus
Cancel = True
ElseIf IsNull(Me.[subPhoneEntry].Form![cmbPhoneTypeID]) Then
msgbox "You must enter a Phone"
Me.[subPhoneEntry].Form![cmbPhoneTypeID].SetFocus
Cancel = True
Else: Cancel = False
End If
If I can't figure this out, I am thinking of making a new address type and phone type. Call it "none chosen" and then make that a default selection. Then one would always be chosen...I think. I would like to avoid that though.
Thanks again.
I have a downtime database that tracks units down, time on, reason down, etc. When a unit goes down, I log the name of the unit and the time down in separate fields. When a unit goes back on line, I log the time on and the reason in separate fields. All this is in a form. When the unit goes on line, I want the user to be forced to enter a reason only after he has entered a time on, not before. The “reason” field must be left blank until the unit is on. How do I do that? I have searched the forum for this and have not found leaving a field blank based on another field’s data. I will supply whatever you need to help me. Thank you.
View 6 Replies View RelatedWhen I click a tab (table,queries,reports or module) I receive an error that says (" isn't an index in this table. Look in the indexes collection of the TableDef object to determine the valid index names). Please help.
Thanks,
Kacy
Hi all,
i have been sick for quite a while,But back on track now.In the past i was building a car event db and would like to finish it.I have checked it out and there seems to be a few errors that i can't remember anything about.
you must have outlook installed otherwise some missing references come up.also there seems to be a problem with the date on the invoice form,the backup section in utilites.
If someone could have a look and point me in the right direction it would be greatly appreciated.
The file is access 2003 format
I have database which has worked normally, and just made a couple of amendments
I then go to edit a form (which I had not amended) and get an Access has an fatal error etc.
I reopened it. and repaired compiled etc, but absolutely impossible to get into this form. Access crashes every time I try to do something with it. I can't even delete the form.
(This was A97)
There's no problem, I just got my last back up, and imported the changes I had made from the faulty dbs -
I just wondered if anyone else ever got these issues, and what they did about them.
Hi All,
I've created an Access Database off of an old Fox Pro System. What I need to do is take the data from the tables in the old system and convert them in Access '03. I have roughly 4600 records to transfer.
When I copy the data from the old system and paste it in an Access table, only 300 or so records are pasted. Also, when I check the paste errors, there are less than 100 in there. Where are they going?
Some of the files aren't pasting because there were input errors on some. Others are linked with a department and Acct number and some of those are no longer current, so another reason why some of these records aren't pasting. This is a problem, but should only be around 500-1000 records tops.
I have noticed if I paste less at a time, I get less errors and more are actually copied through, but I don't know exactly how many I should do at a time?
Some questions I have are:
1. How many should I paste at a time?
2. Am I missing something with the paste errors, is there like a setting I can adjust?
3. Is there a better way to convert this old table into a new table?
Any suggestions or comments would be great because I have been stuck on this for a while and I DO NOT want to input these records one by one.
Thank you in advance,
Josh
Here's an email I sent to one of my friends seeing if he had any insight. I might explain my problem a little better here:
Purchasing Department has an old Fox Pro Contract System. I made this same system into Access and it works great. My problem is now converting the tables from the old system into the new system. There are approx. 4200 records. I have tried pasting them all in together and also sections at a time, but everything I do so, I get more or less copied through, and more or less paste errors displaying the ones that hadn’t copied over.
The reason some are not pasting in is because of old user input errors. Another reason is department and account numbers have changed therefore when the copy takes place, if Access doesn’t recognize an Acct number, then it cannot relate it to a department and will not paste in. I’m fine with that though. The problem I’m having is I’m always getting different amounts of errors each time I do this. For example, I did the first 500 records. Had 21 paste errors, all the others pasted fine. I then tried to do less so I copied the first 400 and only received 3 errors, but then I did the next 400 after that and only 200 pasted through and it only displayed 20 or so paste errors. I think there are roughly 400-500 bad records so I’m not understanding why all but those 400-500 paste.
We have an access db that has been working fine for ages and now when we click on a New Record button we are getting the following error:-
Run time error 2105 - you can't go to the specified record. erroring out on the line DoCmd.GoToRecord , , acNewRec. We are not aware of anything changing on the PC that the db is sitting on. I have spent the whole day searching the web but have found nothing helpful.
I have compacted and repaired the db, even went to the form/table security to see if all users can edd/add.
Any help greatfully received
I've created an Access Database off of an old Fox Pro System. What I need to do is take the data from the tables in the old system and convert them in Access '03. I have roughly 4600 records to transfer.
When I copy the data from the old system and paste it in an Access table, only 300 or so records are pasted. Also, when I check the paste errors, there are less than 100 in there. Where are they going?
Some of the files aren't pasting because there were input errors on some. Others are linked with a department and Acct number and some of those are no longer current, so another reason why some of these records aren't pasting. This is a problem, but should only be around 500-1000 records tops.
I have noticed if I paste less at a time, I get less errors and more are actually copied through, but I don't know exactly how many I should do at a time?
Some questions I have are:
1. How many should I paste at a time?
2. Am I missing something with the paste errors, is there like a setting I can adjust?
3. Is there a better way to convert this old table into a new table?
Any suggestions or comments would be great because I have been stuck on this for a while and I DO NOT want to input these records one by one.....
I have an unbound combo box that is sporadically failing with an error message when an entry in its pulldown list is selected. The error message is to the effect: 'The value you entered isn't valid for this field' .
The record source for the combo box is a table/query which is updated by an event triggered by another control on the form. That code also sets the Format property of the combo box to match the native Format of the field from which the combo box is retrieving the data. So data from Yes/No field, a date field, or a currency field, for example, display properly in the revised combo box. When the combo box is populated from a source field that has no special format (the usual case, as with text fields), the code resets the combo box's Format to "".
Again, the combo box has no control source (it is unbound). So, the only culprit for an error message like this that I can think of is this Format property. Yet, when the error occurs, the combo box's Format is properly set to "", which should permit any kind of data, and I try entering any kind of data at all -- all data is rejected with this error message.
Is there any thing else I should look at?
Hi,
this is probably an old one but its catching me out. I'm writing a sports club database in ASP.
When users submit their club details in the form to Access, I get errors if it has the characters ' in any of the fields.
I've managed to get around " by replacing it with "". However, this doesn't seem to work with '
I'm new to ASP... anyhow the part of my form which deals with this is:
Form_style = Trim(Replace(Request.Form("style"),"""",""""""))
Form_club_name = Trim(Replace(Request.Form("club_name"),"""",""""""))
Form_name = Trim(Replace(Request.Form("name"),"""",""""""))
Form_website = Trim(Replace(Request.Form("website"),"""",""""""))
I also do a bit more error checking on the email address, for length, @ etc. This bit seems to work ok. However,
Any tips on how to improve the error handling for ' or " before its submitted to access is appreciated.
Thanks
My Access 2000, created the dummy's way, has been perfectly happy with its query expressions. But when I create a module calling up these queries, I get Compile Error on many perfectly legal expressions such as Left([pfn],1).
I've tried repairng the database but to no avail.
What is going on, please?
Btw, DBicken, it's when I include your text concantenation code (Posting 6/21/06: "A whole lotta text/stitching") but I don't think it's your fault: it's just exposing it. I can't try your suggestion until I solve this problem.
The problem is that when an admin update the database from a web application that I created in .asp, it gave an error message.
The only way to fix this is to restart the IIS services and others that depend on IIS. But this is not practical.
Code:Microsoft OLE DB Provider for ODBC Drivers error '80004005'[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query./FoodMenu/CalendarPopUp.asp, line 62
Here is the code for 'CalendarPopUp.asp'. Its quite long.
Code:<%@ Language = "VBScript"%><%Option Explicitdim NewEvent 'As Booleandim EventDate 'As Datedim EventStartTime 'As Datedim EventEndTime 'As Datedim EventBLD 'As Stringdim EventDescription 'As Stringdim EventDateString 'As String dim EventID 'As Stringdim Conn 'As ADODB.Connectiondim rstPopUp 'As ADODB.Recordsetdim SQL 'As Stringdim BLD 'As Stringfunction SafeQuote(InString)safequote = replace(InString,"'","`")safequote = replace(InString, vbCrLf, "<br>")safequote = replace(InString, " ", " ")end functionfunction SafeDate(DateValue)if isdate(DateValue) thenSafeDate = "#" & DateValue & "#"elseSafeDate = "NULL"end if end function%><!--#include file="include/incConn.asp"--><%'***** Save Form Data *****if Request.Form("cmdSaveRec") = "Save Event" thenif len(Request.Form("txtDetails")) > 0 thenif Request.Form("NewEvent") = "True" thenSQL = "INSERT INTO tblCalendar ( [Date]"SQL = SQL & ", StartTime"SQL = SQL &", EndTime" SQL = SQL &", BLD"SQL = SQL &", Description ) "SQL = SQL & "VALUES ( "SQL = SQL & SafeDate(Request.Form("txtEventDate")) & ", " SQL = SQL & SafeDate(Request.Form("txtStartTime")) & ", " SQL = SQL & SafeDate(Request.Form("txtEndTime")) & ", " SQL = SQL & "'" & SafeQuote(Request.Form("BLD")) & "', "SQL = SQL & "'" & SafeQuote(Request.Form("txtDetails")) & "' );"'Response.Write sqlConn.Execute SQLelseSQL = "UPDATE tblCalendar SET "SQL = SQL & "[Date] = " & SafeDate(Request.Form("txtEventDate")) & ", "SQL = SQL & "StartTime = " & SafeDate(Request.Form("txtStartTime")) & ", " SQL = SQL & "EndTime = " & SafeDate(Request.Form("txtEndTime")) & ", "SQL = SQL & "BLD = '" & SafeQuote(Request.Form("BLD")) & "', "SQL = SQL & "Description = '" & SafeQuote(Request.Form("txtDetails")) & "' "SQL = SQL & "WHERE ID = " & Request.Form("ID") & ";"'Response.Write sqlConn.Execute SQLend ifelseend ifSet rstPopUp = NothingConn.CloseSet Conn = Nothingend if'***** Process QueryString *****if Request.QueryString("NewEvent") = "True" thenNewEvent = TrueelseNewEvent = Falseend ifEventDate = Cdate(Request.QueryString("Date"))EventDateString = Request.QueryString("Date")if len(Request.QueryString("EventID")) > 0 thenEventID = Request.QueryString("EventID")SQL = "SELECT * FROM tblCalendar WHERE ID = " & EventID & ";"set rstPopUp = Conn.Execute(SQL) if not (rstPopUp.EOF and rstPopUP.BOF) thenrstPopUp.MoveFirstEventDate = rstPopUp("Date")EventDateString = cstr(Month(EventDate)) & "-" & _cstr(Day(EventDate)) & "-" & cstr(Year(EventDate)) EventStartTime = rstPopUp("StartTime")EventEndTime = rstPopUp("EndTime")BLD = rstPopUp("BLD")EventDescription = rstPopUp("Description")elserstPopUp.Close Set rstPopUp = NothingConn.CloseSet Conn = Nothingend ifend if%><html><head><%if NewEvent then%><title>Add New Event</title><%else%><title>Edit Event</title><%end if%></head><body bgcolor=#0066a9 text=black><table border="1" width="481" id="table1"><tr align=center><td align=center width="480"><font color=white>Breakfast 8am-10am Lunch 11am-2pm Dinner 6pm-10pm</font></td></tr></table><%if Request.Form("cmdSaveRec") = "Save Event" then%><script for=window event=onload LANGUAGE="VBScript">close()</script><%End If%><table width=480 height=380 border=0><form action=CalendarPopUp.asp name=frmEvent method=post><input type="hidden" name="ID" value="<%=Request.QueryString("EventID")%>"> <tr> <td align=left bgcolor=#0066a9> </td> <td width=55% bgcolor=silver> <table width=100% height=100% border=1 bordercolordark=Gray bordercolorlight=White> <tr bgcolor=silver bordercolor=Silver> <td align=center valign=middle><input type=hidden name=NewEvent value=<%=NewEvent%>> <table height=100% border=0 cellpadding=1 cellspacing=1> <tr> <td align=center><b>Select a Date:</b><br><input type="text" name=txtEventDate Value="<%=EventDateString%>" size=10> </td></tr><tr><!-- <td align=center> <b>Select a Start and End Time:<br>(i.e. 3:00pm) (i.e. 5:00pm)<br></b> <input type="text" name=txtStartTime value="<%=EventStartTime%>" size=10> / <input type="text" name=txtEndTime value="<%=EventEndTime%>" size=10> </td> --></tr><tr> <td align=center><input type="submit" name=cmdSaveRec value="Save Event" size=25> </td></tr> </table> </td> </tr> </table></td><td align=right> </td> </tr> <tr> <td align=center bgcolor=#0066a9 colspan=3><select size="1" name="BLD"><option selected>choose meal type</option><option>Breakfast</option><option>Lunch</option><option>Dinner</option><%=EventBLD%></select></td> </tr> <tr> <td align=center bgcolor=Silver colspan=3> <table width=100% height=100% border=1 bordercolordark=Gray bordercolorlight=White> <tr bgcolor=silver bordercolor=Silver> <td align=left> Please Enter a Brief Description of the Event:<center><textarea rows=9 cols=53 name=txtDetails><%=Replace(EventDescription,"<br>", vbCrLf)%></textarea></center> </td> </tr> </table></td> </tr></table></form></center></body></html><%set rstPopUp = Nothingset Conn = Nothing%>
So, I look up in this site and follow it. No luck. I even follow theis link
Code:http://www.webwizguide.info/asp/faq/server_permissions.asp
But it was already configured that way.
Even when I try to add, it gave me a similar error message.
Code:Microsoft OLE DB Provider for ODBC Drivers error '80004005'[Microsoft][ODBC Microsoft Access Driver] Could not delete from specified tables./FoodMenu/include/incAdminDel.asp, line 21
Here is the code for 'incAdminDel.asp' file
Code:dim TmpString 'As Stringdim Item 'As Stringselect case Request.Form("cmdTopic")'***** Community Calendar *****case "Edit Community Calendar"for each Item in Request.Formif left(Request.Form(Item),6) = "Delete" thenSQL = "DELETE * FROM tblCalendar WHERE ID =" & _mid(Request.Form(Item),7) & ";"Conn.Execute SQLend if
Can anyone tell me what I need to look for?
P.S. This is an Online Food Menu which is inside an 'Employee Directory' that is a web application. When a user open up the 'Employee Directory', it load up the page along with an icon to view 'Online Food Menu'. Kind of like having a web application within a web application. Updating the Employee Directory is not a problem. The issue lies on the admin side where s/he try to update the menu, it give 2 of the above errors that I mention.
It is on a Windows 2003 standard edition machine.
Hi all
Background
I'm appending data using an append query. I have a known error that I'm trying to work around. sometimes when trying to append, the field names are slightly different and I get a prompt for a parameter value. ideally i'd like all the field names to match up...
So.... I'm trying to error trap this.
what I need is some code that will...
if prompted for a parameter, cancel the parameter and goto query 2 (or error1).
I can't seem to use on error because access isn't treating it as an error...
Any ideas are much appreciated.
Damon
Since we have installed the latest Windows critical security updates, upon closing an Access database, the database gets corrupted. Error code is c000020c.
View 4 Replies View Related