Hello out there:
We have a application that imports information from a csv file into our customer order table. After we process the order we have to export this inforrmation to our UPS shipping program to process the shipments.
Problem is that the UPS program only recognizes the abbreviations for state names. Is there a way to run a macro, code or update query that will change the state names to the correct abbreviated version before we do our export?
I have a form with several data fields on it. I also have a button on the form that allows the user to duplicate a record . The reason for this duplication is so that if there will be an additional client record for the same customer, but only one piece of data will need to be changed, it's easier to copy the record and then change the one field.
However, I am getting the following message:
"some of the field names you tried to paste don't match fieldnames on the form"
and then not all data in all fields gets duplicated.
I need to figure this out, but am going nuts with it. If anyone has an idea or two they'd care to toss my way, I would be happy.
Ok, I know you guys have heard about this problem quiet a few times, my search told me that. But my problem has a bit of a twist. The message appeared when I tried to open the database yesterday, so I went to the backups and tried to open them. All the backups for the past month give the same message. The problem is with the FE only, the BE is working fine. The backups are connected to the same workgroup as the FE and BE. Has anyone seen this before and know what might of happened?
I am trying to determine the state that a job is located in. If the ProjectID begins with a 2 then it is in California. If it does not then the job is located in Nevada.
Does any one know of a place online where you can get the tables (for free I hope) for a city state zip comboboxes, or at least the tables for all cities in the US.
If the check box is null (grey) - the default value, I do not want to include it is the sql statement. If I use the code below I get the error 94 Invalid use of null. How else can I exclude this ststement is the check box is grey.
thanks
If CheckStaged Then strWHERE = strWHERE & " AND s.Staged = " & CheckStaged End If
1) how can i have a record automaticly fill in the next date from the privous record? (Example if record 1 has date:10/14/05 i want record 2 to fill in in that same feild 10/15/05?
2) how can i have a reccord automaticly fill in the same number from the privios record? (Example if record 1 has Number :34 i want record 2 to fill in in that same feild 35?
I keep on changing these numbers, so its vitel that it repeats the privous record
"This database is in an unexpected state; Microsoft access cannot open it.
"This database has been converted from a prior version of Microsoft access by using the DAO CompactDatabase method instead of the Convert Database command on the tools menu. This has left the database in a partially converted state.
"If you have a copy of the database in its original format, use the convert database command on the tools menu to convert it. If the original database is no longer available, create a new database and import your tables and queries to preserve your data. Your other database objects can't be recovered."
The database is on the server. Nothing was changed. Someone in our IT department says they simply opened it and then closed it. I think that they had their access program set to compact and repair on close.
I have tried to import the tables but am gettting the same errors.
I have the original database but it is an old backup that is missing data.
I am trying to get the Top 10 NPI's by State and By County based on the Current_Year_Members_Outreached. Below is the query I created but I haven't been able to verify if the results are correct because when I execute it runs and some results show but in the lower right corner of the screen it says "Formatting Page" and seems to be taking forever.
My source table only has 67k records which doesn't seem like a lot. So, I have two questions:
1. Does my query below look correct based on what I am trying to achieve? 2. Is there a better way to right this query to get better performance?
Code:
SELECT A.STATE, A.COUNTY, A.NPI, A.[SumOfCurrent Year Members Outreached] FROM Qry_Provider_Distinct AS A
I need help with the code for a combobox. The thing is that I've created a form where the user has to select the country, then the state and finally the city. What I need is that once the user selects the country, the combobox of the state shows only the states of that country and then the combobox of the city shows only the cities of the chosen state. I'd appreciate all the help given. Andrew.
I have a database will all the FM stations in the USA/Canada, I wish to plot the frequency and calls to the correct city on the state map as per the attachment, can it be done? If so, what are my steps and what maps can I use?
I'm stuck on something that should be simple, but maybe it isn't:
Problem 1:
To speed data entry in a contact table, I want an entry in the Zipcode (postal code) field to automatically populate the City and State fields from the data in a Zipcode table (Zipcode-PK, City, ST (2 letter code)). The Zipcode table has only one city /st for each zipcode, so it can be the primary key (always a five digit number, sometimes with leading zero(s)).
Sometimes there are several names for a City associated with a particular Zipcode, so I want the City field to be able to be overwritten with a different city name if the data entry person needs to do that. Alternatively, I could establish a separate primary key for the zipcode table, and allow several choices for the rare zipcode that has more than one city name in use.
If I did that I wouldn't have to store the city and st in the contact table (which is better database design, I realize), but I really do want to be able to store the city and state in the contact table because there will be times when the right zipcode isn't known, and the city and state are known, and forcing the input of a possibly incorrect zipcode would be undesirable.
Problem 2: I've got a table with 4,000 records where I have the zipcode, but the city and state information were lost, and I want to be able to re-populate those fields by writing, I guess, an update query that taps the info in the zipcode table.
Problem 3: When entering addresses, sometimes there won't be info about a certain zipcode, and I need some automatically generated dialog that allows adding city and state info for a new zipcode that won't slow down the entry of contact records.
Any help would be greatly appreciated. Thanks in advance. Send an email if you prefer.
Apologies first if there is an easy answer to my problem.
I have set up an option group with 3 checkboxes with choices Yes, No, Maybe. On data entry you can click any of the three boxes but after clicking one how do you revert to a situation with all 3 checkboxes cleared. I don't want to use a default checkbox as that would add another row but I just want to clear out the boxes so it looks as though none of them have been ticked.
I am trying to create a suitable expression, either on the form itself or in the underlying query, that calculate the balance after each transaction, like a bank statement. What is the best way to do it?
i want to have a button where the operator can cancel when on a current record. i want an if. if form has changes made but not saved then undo else close.
I'm trying to do something with Excel and Access. From Excel 2007, I need to open an Access database exclusive, import from Access to Excel a table, do some work within Excel, and then start a macro within the Access database. Briefly, here’s what I have
1) To open the database exclusively:
Set connDB = New ADODB.Connection With connDB .Provider = "Microsoft.ACE.OLEDB.12.0" .Mode = adModeShareExclusive .Open strdbpath 'path to database End With
2) To import from Access:
strSQL = "SELECT * FROM [" & tn & "];" 'tn is Access table If connDB.State = adStateOpen Then Set objRS = New ADODB.Recordset objRS.Open strSQL, connDB, adOpenForwardOnly On Error Resume Next objRS.MoveFirst If Err.Number = 0 Then On Error GoTo 0 fieldCnt = objRS.Fields.Count For fieldNum = 0 To fieldCnt - 1 ws.Cells(1, fieldNum + 1).Value = objRS.Fie(fieldNum).Name Next fieldNum ws.Cells(2, 1).CopyFromRecordset objRS End If End If
3) To start a macro:
DoCmd.RunMacro "Daily Import"
Everything seems to work until the ‘DoCmd’ statement where I get a “You can’t carry out this action at the present time” error message.
I want to get the sum of sales of the top 5 best agents by state.
I have been able to do the following, however, this only gives me the state of "vic". I want it to show all states.
SELECT TOP 5 filedata.Debtor, Debtors.FullName, Sum(filedata.NetTotal) AS SumOfNetTotal, Debtors.State, Count(filedata.BorQ) AS CountOfBorQ FROM filedata INNER JOIN Debtors ON filedata.Debtor = Debtors.Code WHERE (((filedata.BorQ)="b") AND ((filedata.DepDate) Between [Forms]![F-financemenu].[startdate] And [Forms]![F-financemenu].[enddate])) GROUP BY filedata.Debtor, Debtors.FullName, Debtors.State HAVING (((Debtors.State)="vic")) ORDER BY Sum(filedata.NetTotal) DESC;
In ACC2010: A subform has a control for date of birth with an AfterUpdate Event. When the date picker is used to populate the control the user must either click outside the control or press the tab key to trigger the control's AfterUpdate event. This event runs some calculations, the results of which are stored in a separate table. When the calculations are finished and focus returns to the form, the form's state depends on whether a keypress or a mouse click was used to trigger the event.
The form's state is indicated by whether calculated controls on the form are empty - mouse clicks leave contents as before the event, tab causes the contents to disappear. In addition, if a keypress is used the user must click twice to trigger the form's BeforeUpdate event. A single click is required to trigger that event if a mouse click occurred after using the date picker.
How to get thekeypress to behave the same as a mouse click?
I tried this code to change the label to display the full name of the state on the form. Is there a shorter way than putting 49 more states after the if?
[CODE]Private Sub cmdStates_Click() DoCmd.OpenForm "frmChurchesAll" DoCmd.ApplyFilter "qryFindState" lblTxtSt = txtState If txtState = "FL" Then lblTxtSt = "Florida" End If lblTxtSt.BackColor = 15658720 lblTxtSt.Visible = True lblStatesof.Visible = True lbAllChurches.Visible = False End Sub /CODE] D7
when i select rows in a ListBox control on a form, using access VBA, some rows behave exactly the opposite of how they're supposed to behave-- they APPEAR unselected when i select them, and selected when i unselect them.
Code: oListBox.Selected(lngRow) = True
However, when i check their Selected property with VBA they return the expected value (meaning the row Selected value is TRUE if i set it to TRUE, if tho it LOOKS UNselected).
Code: Print oListBox.Selected(lngRow) ->True
Access ListBox Control Under VBA Control Behaving Wacko - YouTube.It seems that the more times i requery the listbox, the worse the problem gets.
Code: oListBox.Requery
This problem seems unrelated to the number of times i select rows. Only seems related to multiple requeries. At first, it does not happen. Then with repeated requeries, the problem gets progressively worse. Note, 'Row Source Type' is set to 'Table/Query'. 'Multiselect' is set to 'Simple'.
i'm not using the "Form_" syntax anyplace (which can created unintended instances of forms). this listbox source-query contains a VBA function in a module, which in turn calls the code-behind of another form.
Code : .OpenDataSource Name:=CurrentProject.FullName, SQLStatement:="SELECT * FROM [qryMailMerge]"
The error (Error has occurred: The database has been placed in a state by user 'Admin' on machine 'W74XXXXXX' that prevents it from being opened or locked) appears in the Word document.
Code: Public Sub CreateWordToPDF(strWordFile As String, strPDFFile As String, strSQL As String) 'Call CreateWordToPDF("C:Doc1.doc", "C:Doc1.pdf", "SELECT * FROM [qryMailMerge]")
Dim objWord As Word.Application Dim docWord As Word.Document 'Open MS Word using early binding. Set objWord = New Word.Application Set docWord = objWord.Documents.Open(strWordFile)
Everyone know this error? the database has been placed in state by user "Admin"!
We are using a shared database in Access 2003 that is still in Access 2000 format. When more than one user has the database open (by using the vb.net), the following error message appears "The database has been placed in a state by user 'Admin' on machine XXXXX that prevents it from being opened or locked."
How to slove this error? please help! Urgent!:confused:
I am trying to do the following If any one of you have experienced Abacus ticketing system, each reservation is comprised of a group of names as follows
I have to copy the above data from the abacus tcketing system into a text box. My question is how can i devide the above three names into three seperate text boxes.
