Support After Design - Record Import And Other Issues
Jan 24, 2006
So I finished my database :) ... then it was time to make it look as if it wasn't an Access DB. I read all the very helpful threads on converting to MDE, hiding access window, starting up my form, placing an icon in my main form title bar so I'm almost set but before I go ahead here is my concern:
So, basically I will make the application look like a stand-alone product and prevent the user from doing anything but using my forms. However, lets say in a month time the user wants to add some feature. Yes, I can keep the original DB and add the feaure on my version but what about all the records that were created in the meantime? How will I be able to import them then, especially if all the tables have autonumbers (which I don't know how to reset to blank in my original) and serve as the linking field for many records? All the relationships between the records would be messed up.
(I'll also have to deal with the security and set it up since one of the tables will store credit card numbers.)
I'm trying to build a table structure for this database. Heres some background information about the business:
The business is a service business. We visit the customer's location and run tests on whatever water systems they have. Each customer is unique in that they could have any combination of systems at their site. They can also have more than one of the same type of system. The test results are the data that I need to record and store for future access. Each customer is visited on average once or twice a once a month. So there should not be more than 1-2 entries of data for each system for each customer per month.
For example customer RUTGERS might have two systems labeled HWS and CHWS. customer BMSQUIB might have three systems labeled HWS1, HWS2, and CHWS.
What I need to do with this information is go into the records of service visits, and retrieve for example, the last 4 visits of a specific system and prepare that information to be printed in a report along with their contact information.
I have come up with three tables to do this:
Customers Table contains: Customer ID (pk), contact information data.
Systems Table contains: System ID (pk), Customer ID, System Name
Service Records Table contains: Record ID(pk), Date, System ID, Data
My thoughts were to have a table to contain customer information(each customer with a unique ID), a table to contain system information for each customer (each system has a unique ID), and a table to store the results of every service visit for each system(each individual visit has a unique ID)
Please critique this table design. If you think its sufficient, perhaps you could lead me in a direction pertaining to how to retrieve data on the most recent 4 visits (last 4 entries) for a specific SINGLE system from the Service Records table. I would assume that you would need to use a query and then get the data from that and put it into a form.
1) HOw can I move a record from one talbe to another when a specific drop-down option is selected. Example: a combo box with "Active"; "Pending"; "Closed" I want all closed recoreds in a table just for closed records to reduce clutter. They're not going to be referenced often if ever, we just want them so we have them if we do need them.
2) How can I prevent changes to the design of a form/query/report/etc.
I would like to build a query that has a calculated field, involving value from a previous record. I have five columns, Year (sorted ascending), InvestmentRate, StartingBalance, AnnualContribution, and InterestIncome.
The first row, being the smallest year value, uses a previously defined elsewhere starting balance, annual contribution, and interest income. This is recognized via an Iif statement and establishing the first set of values which the second row should use to calculate.
The second record, and until the end of the query, automatically calculates the new starting balance value based on the sum previous row record's starting balance, annual contribution, and interest income.
Furthermore, for the second record, and untill the end of the query, the InterestIncome is calculated value incorporating the second row's StartingBalance value (which is the product of the sum of the previous record's values).
This calculated referencing to the previous record is causing difficulties.
I had copy/paste from MS Word, FileMaker Pro and DOS into MS Access Memo field. They act very strange. Is it t a way to copy these text out to some othher format to script them to MS Access supported format and paste them back into MS Access?
I want to create a database using MS Access that can be accessible by 80 users for information search purpose. At the same time at least 20 users out of 80 can make modifications. Need suggestion will that be possible in access.
The database will store in shared drive. Also want to know how to lock a record let say if user1 has access certain record for modifications than the second user can access database but not allow to modifying the record which is already under modification phase.
I have made a couple of posts about this and had no response as yet. Maybe i didn't explain it well.
I have a form that logs emails. The emails are either "to" or "from" a person. I want to have two queries that pick out either to or from. Now each person i know sends me several emails over time obviously but i am only concerned with their last email or MY last email to THEM because that determines if i should email them back or if i am waiting for them to contact me. At the moment i am having problems filtering out the last record for each person which determines whether i need to email them or they need to email me. I also use the record to log when the last mobile text was sent, phone call etc so it is not just emails. The following is what my formsfields look like
CommunicationID Communication type TO or From Date Day (automatically taken from the date) Subject
I cannot for the life of me get the filter to work properly. Currently i am either getting more than one record for each person or the results are jumbled up. I really need a better understanding of how to use the totals thingy in the querys design view. I'd really appreciate some help on this. Thanks....Ross :confused: :confused: :confused:
Hello all. I've tried searching the forums as well Google for an answer for my problem, but perhaps I'm not searching on the correct key words. Would someone please point me to a post that addresses the below issue. My company has a customer service (CS) application where our CS agents document CS requests. For reporting purposes, they drop a text file containing open service requests that I then import in Access 2003 so I can analyze the data. I've had the vendor use È (ALT+0200) as the delimiter and double-quotes to enclose text. This has been working well until the application vendor upgraded the app last week. Now, whenever a user hits the [Enter] key to create a new line in the comments section of the application, a new line is created during the import process into Access. When I view the text file I can see the Enter Character because it is displayed as (except it looks more like a rectangle standing in its short side.) I thought if creating a macro to run on the text file to delete the enter characters, but I can't find a way to systemically find the character. So far, the only solution on my end is to manually delete the characters, but that's unrealistic because there are thousands of records. Thank you in advance for pointing me to a helpful post or commenting here. Stephen
Hi, sorry about the unwieldy title: I appear to have reached a limit while adding fields to a fixed width text table which is imported to Access on a monthly basis using Import Conditions to define the field start points and lengths. Using the Import Conditions wizard, I cannot add any break points beyond the 495th character. Is this a known limit, or has anyone found a way around? I've tried both Access '97 and 2002. Any advice greatly appreciated.
I am thinking about creating a DB using Access for a business. However, my wife mentioned to me that her company is looking at software for database work and has some Access DB's they are trying to convert because, according to a technical support person, Microsoft intends on getting rid of Access as a DB! I am posting here to ask anyone, professional or otherwise, if anyone has heard that Microsoft intends to stop supporting or implementing Access. Is Access 2003 the last version? I haven't been able to find out anything along this line. I thought, maybe Microsoft is thinking about supplanting Access with SQL Server. Before I go to the toil of building a DB system in Access, I want to know I am not wasting my time doing it. If anyone on this forum has heard a thing about Microsoft dumping Access, please let me know. Thank you. :o
Hi, I have a database which contains fields with information in many different languages. I seem to lose my japanese content from time to time. I cut and paste the information from a Word document and paste it into an Access field. It seem to work and I get the japanese text pasted. But I have lost it and now I just have all text replaced by small squares. How can I retrieve my japanese text?? What should I be looking for if I want to maintain a database in MS Access using Japanese, Chinese and other foreign language text in it? Bjorn
What I wanted to do was try and import from 5 excel sheets into one table and merge them into 1 record.
E.g. I have pupils with test results for maths, English and ICT.
I have 3 spreadsheets called Maths, English and ICT and I wanted to merge them into the test results table all under that specific pupil.
Also I've been searching how to create an import button on my form as I have locked all the ribbons but can't find a simple way, and especially if I want the user to specify the file path.
I want to disable a button right after click it. Because I could not disable a control that has got the focus, i tried to shift the focus to another control; however, all controls that I tried to shift the focus to don't support the method (SetFocus = true).
I want to do this on a subform's control, but I keep getting this error: Object does not support this property or method.
I have an access database that I developed while working for tech support at earthlink a while ago. If anyone is interested, I'd be happy to clean it up and post it in the examples. Some of it's features include: Tabbed Browsing Posting and manipulating web forms HTML Scraping Injecting information into other programs via APIA screenshot of it is below
i'm getting this message when i try and change a record in a table via a recordset... but i am using CursorType = adLockOptimistic which i thought let you make changes to the table
here is my code so far
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.CursorLocation = adUseServer rst.CursorType = adLockOptimistic rst.Open "TBL_TmpSubmission", CurrentProject.Connection
If rst.RecordCount > 0 Then Do While Not rst.EOF MsgBox rst!PropertyType, vbOKOnly, "debug" If DCount("[PropertyType]", "[TBL_PropertyType]", "PropertyType = '" & rst!PropertyType & "'") <> 1 Then rst!PropertyType = DLookup("[PropertyType]", "[TBL_PropertyType]", "IDPropertyType = " & rst!PropertyType) MsgBox "property changed", vbOKOnly, "debug" Else MsgBox "good property", vbOKOnly, "debug" End If rst.MoveNext Loop End If
rst.Close
am i using the wrong combination or cursor and lock type here? reading the help it seems i should be able to make changes to the table.
I am writing a query that is master-detail in principal. But need other data from other 2 tables. I wonder if I can construct the query so that it contains the result set of more than one SQL statement. Thank you for your kindly help.
I get the message "The expression On Click you entered as the event property setting produced the following error: Class does not support Automation or does not support expected interface"
I receive the message on a PC running Windows 7 Professional using the Access 2013 Runtime. This pc does not have Access 2013 installed.On my pc, I do not get the error. I have Access 2013 installed and run Windows 7 Professional SP1. The "code" which gives the error is as follows and is invoked by clicking a button on a form
Code: MsgBox "1" Dim rst As ADODB.Recordset MsgBox "2" Set rst = New ADODB.Recordset MsgBox "3"
[code]....
The error takes place after Msgbox "2" and before Msgbox "3".The strange thing is that I can run without a problem a sophisticated software package on the pc which gives the error, using Access 2013 Runtime. This package I converted from Access 2003.
I and trying to link to Outlook 2007 using the "External Data/More/Outlook Folder option and keep getting the "Object Class does not Support the Set of Events" error message.
I can link to a DBF and Paradox file without any problems.
I am running Office 2007 Professional and Access 2007 and Windows 10.
I have also done the following:
1. Removed Office 365 2. Decompiled the Access VBA code and corrected any issues 3. Compacted/Repaired the database
I have attached a screen shot of the references that I have selected.
This is a new setup as I recently purchased a new PC and Windows environment. I still have the old PC running Windows XP with the same configuration and it also experiences exactly the same error message.
I have this below which some people cant run and some people can. All are using 2010 runtime version. Apart from the one guy who can run the macro who has full 2010 version. I have 2010 runtime installed and i can also run the macro fine.
Private Sub Option12_Click() On Error GoTo Option12_Click_err Dim CntlPay As String Dim Lable As String CntlPay = "D"