I inherited this database and I can't figure this out so I'm looking for some causes.
the database is in one .mdb file, the interface in another and links to the database via the linked table manager.
The tables aren't normalized, nor was there any attempt at relational integrity (Strike 1). Thats obvious
But what isn't so obvious is that data in fields just disappears, it'll get entered, verified and a couple weeks/months later, its just gone and no one claims responsibility for taking it out (and in 99% of cases, we wouldn't)
It is a multi user database but there are only 10 people in the office and I doubt more than 3 are ever connected at once. And most of the time is only one of us at a time based on our schedules.
Anyone have any ideas? I'm going to advise the bosses that we take the time to start over with sql server, normalize the tables and take better care of developing the forms (in .net)
Whenever I come to a record that does not have any data in my subforms, then (as you can expect) my subform controls disappear.
My subforms have "Allow Additions" set to YES.
The subform tables are linked through a LEFT JOIN.
I would like the controls to stay visible even when there are no records in the subforms.
I tried to set the subforms ID to the main forms ID thinking that this will at least generate an autonumber to make the subforms controls visible, but it did not work
I have two related issues with reports. That is the data shows in report view and then disappears. The first is the simplest. I have a report that has a query recordsource and I calculate totals and populate the report in the OnLoad event using VBA code. This displays perfectly in "report view" and prints or exports fine. My issue is that after the print or export, the report has no data in my fields in "report view". It is like it is reloading the report without invoking the OnLoad event. When I close the report and re-open in report view, everything is fine again until I print.
The second issue is related. I have another report that is a bit more complicated. Due to the number of controls (I use many text boxes to display my calculations), I am using 3 subreports to get around the limitations in the number of controls. In report view, the report looks fine, but when I scroll down to the bottom and then back up, the text boxes in the subreport sections are empty. The parts of the report not in subreports is fine. By using "Msgboxes" in events, I discovered that when I scroll down, the subreports close, then re-open when I scroll back up. Again, due to an Access limitation of 22 inches per section, the I placed the subreports in the "report Header" section. It appears that my problem occurs when I scroll down to the "detail" section of the main report.
I created a database which was working well. I then added my data and it still seemed to be working well. Since then I have made quite a few other changes, but only to queries realting to the subform.
When I cycle through my main records the subform shows and displays all the info. But when I get to the end to add a new record, the subform disappears.
I've created a form and when i create a user it goes through without a problem. However when i try to create another user, my code somehow deletes the last record. Where am i going wrong?
Code:Option Compare DatabaseOption ExplicitPublic s As StringPublic Check1 As StringPrivate Sub cmboSC_ID_AfterUpdate()Me.txtSC_ID = cmboSC_ID.Column(0)Me.txtSC_NAME = cmboSC_ID.Column(1)Me.txtSC_PASSWORD = cmboSC_ID.Column(2)Me.txtSC_LEVEL = cmboSC_ID.Column(3)Me.txtSC_PROGRAM = cmboSC_ID.Column(4)If Check1 = "DEL" ThenElseCheck1 = "MOD"End IfMe.txtSC_ID.Visible = TrueMe.txtSC_ID.SetFocusMe.cmboSC_ID.Visible = FalseMe.cmboSC_ID = "" Me.cmdUpdateADO.Enabled = TrueEnd SubPrivate Sub cmboSC_ID_GotFocus()cmboSC_ID.RequerycmboSC_ID.DropdownEnd Sub Private Sub cmdClear_Click()Check1 = ""Me.txtSC_ID = ""Me.txtSC_NAME = ""Me.txtSC_PASSWORD = ""Me.txtSC_LEVEL = ""Me.txtSC_PROGRAM = ""Me.txtSC_ID.Visible = TrueMe.txtSC_ID.SetFocusMe.cmboSC_ID.Visible = FalseMe.cmdDel.Enabled = TrueMe.cmdUpdateADO.Enabled = FalseEnd SubPrivate Sub cmdClose_Click()DoCmd.CloseEnd SubPrivate Sub cmdUpdateADO_Click()On Error GoTo ADOErrhandlerDim ADOrs As ADODB.RecordsetDim sSQL As String 'sSQL = "Select * from tblSecurity;" 'Set rsADO = New ADODB.Recordset'rsADO.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockBatchOptimisticIf Check1 = "ADD" ThensSQL = "tblSecurity"Set ADOrs = New ADODB.RecordsetADOrs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimisticWith ADOrs .Fields("SC_ID") = Me.txtSC_ID.Fields("SC_Name") = Me.txtSC_NAME.Fields("SC_Password") = Me.txtSC_PASSWORD.Fields("SC_Level") = Me.txtSC_LEVEL.Fields("SC_Program") = Me.txtSC_PROGRAM.UpdateEnd WithSet ADOrs = NothingMsgBox Me.txtSC_NAME & " Added" ElseIf Check1 = "MOD" ThensSQL = "Select * from tblSecurity where tblSecurity.SC_ID = '" & txtSC_ID & "';"Set ADOrs = New ADODB.RecordsetADOrs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimisticWith ADOrs .Fields("SC_ID") = Me.txtSC_ID.Fields("SC_Name") = Me.txtSC_NAME.Fields("SC_Password") = Me.txtSC_PASSWORD.Fields("SC_Level") = Me.txtSC_LEVEL.Fields("SC_Program") = Me.txtSC_PROGRAM.UpdateEnd WithSet ADOrs = NothingMsgBox Me.txtSC_NAME & " Edited" ElseIf Check1 = "DEL" Then sSQL = "Select * from tblSecurity where tblSecurity.SC_ID = '" & txtSC_ID & "';"Set ADOrs = New ADODB.RecordsetADOrs.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic ADOrs.DeleteSet ADOrs = NothingMsgBox Me.txtSC_NAME & " Deleted"ElseMsgBox "An Error Occurred. The system doesn't know which action to take"End IfSet ADOrs = NothingCall cmdClear_ClickExit SubADOErrhandler:Select Case Err.Number Case ElseMsgBox Err.Number & vbCrLf & Err.DescriptionEnd Select End SubPrivate Sub txtSC_ID_AfterUpdate()Dim DAOdb As DatabaseDim DAOrs As DAO.RecordsetDim sSQL As StringDim s As StringSet DAOdb = CurrentDb()s = "Select * from tblSecurity where tblSecurity.SC_ID = '" & txtSC_ID & "';"Set DAOrs = DAOdb.OpenRecordset(s)If DAOrs.EOF Then'New recordCheck1 = "ADD"txtSC_ID.SetFocusElse'Exisitng RecordMe.txtSC_ID = DAOrs("SC_ID")Me.txtSC_NAME = DAOrs("Sc_Name")Me.txtSC_PASSWORD = DAOrs("SC_Password")Me.txtSC_LEVEL = DAOrs("SC_Level")Me.txtSC_PROGRAM = DAOrs("SC_Program")Check1 = "MOD"End IfDAOrs.CloseDAOdb.CloseMe.cmdUpdateADO.Enabled = TrueEnd SubPrivate Sub txtSC_ID_DblClick(Cancel As Integer)Call cmdClear_ClickMe.cmboSC_ID.Visible = TrueMe.cmboSC_ID.SetFocusMe.txtSC_ID.Visible = False End SubPrivate Sub cmdDel_Click()Call cmdClear_ClickMe.cmboSC_ID.Visible = TrueMe.cmboSC_ID.SetFocusMe.txtSC_ID.Visible = FalseCheck1 = "DEL"End Sub
In the form view of the db I have a bound box for an embedded ole object I use it to embed Word documents for each db entry (The database is for works of art and the Word document is the description of each work of art which often contains Chinese Characters, hence the Word Document)
The box is set to show an icon (The Word Icon) Upon entering the data and moving off the record, subsequent return to to the record sometimes shows that the icon is gone from the box, it is blank. Clicking the blank box brings up the Word Document but then closing the Word Document and re-clicking the blank box dos not bring up the Word Document. The standard message saying that the ole object is empty appears and all the data from the Word Document is lost.
this has only happened since moving to Office 2007. I upgraded the db to 2007 format and we are using Word 2007.
A Google of the symptoms doesn't bring any meaningful results, does anyone have any ideas?
I tried to keep this as short as possible (which wasn't easy) if you need more info just ask
I have a calculated field in a form that calculates the amount due. When I open the form or the record and the record has an amount due of 0, then the field says "$0.00" for a second and then disappears. Any idea why this is so? This becomes a problem because $0.00 doesn't appear on any reports either. Any help would be appreciated!
In Access 2010, I can't get a form to show up in Pop Up mode without it disappearing. Tried several solutions (most from this message board) with no luck, including:
Set Modal = no Set Auto Resize = no Set Auto Center = no Set Border Style = dialog
There isn't any VBA set to run when the form opens, only on button clicks.I'm able to call out the form to open as acDialog in vba, but this blocks additional code I need to prepopulate the form.So far this happens on all forms I try to set as Pop Up.
Hello, MS Access 2000 sp3 When adding a new record to an order file, I have a default value on the ordnum field. It also has a format = "9999-999". The default value is "0603-". When the user clicks on the new rec nav button, the form has the correct default in the ordnum field "0603-". However, when the user trys to key in the remaining 3 digit sequence number (.i.e. result 0603-123), the default value disappears as soon as the use startes to type the first character. The table source is a query that links the order table to the customer table via cust num. When i use only the order table as the form source, this works ok, no issues but i see this issue when i am using a query joining two tables. Does anyone have any ideas why or how I can correct this? Thanks!! zip file attached with test database
I am making up how to create a database as I go along so I am sure some of the techniques I have used are a bit unconventional! However, I wanted to use data from 2 different tables as control sources, so changed the record source to a query that combined the two tables. I thought this was correct as I could then apply control sources from both tables to fields.
However, now when I click out of design view and into form view, the form/database dissapears. Does anyone know why & how I could resolve this?
I have a calculated control on a form that is summing other controls and then a subtotal on a subform. This is access 2007
The calculated control does not display the value until I enter a value other then the default in all of the source controls. If I hit the "refresh" button, the value appears in the calculated control for a moment, then disappears.
I have set the default of the source controls to be 0, so none are null.
the calc control value does not stay until I put a value in the subform and its subtotal thus has a value
I have a simple SQL query that is e-mailed when there are >0 records. Fairly consistently, the SQL code in the query is wiped out and the query fails. It happens consistently to this query, even though I have many similar queries. I am running Access 2013 in Windows 7
SELECT AR.[Inv#], [History Header].[PO#], AR.[Customer Name], AR.Date, AR.Mdse, AR.Freight, AR.Amount, AR.[Due Date] FROM (AR INNER JOIN [History Header] ON AR.[Inv#] = [History Header].[Inv#(number)]) LEFT JOIN [Advanced Engineering Payment Reminder Sic]
For example if I open up the Access database on my left monitor, then move it to my right - whenever I switch from design to form mode the form disappears. Moving Access back over to the left monitor will not show the form. Minimizing all windows except Access won't show it either. If I switch to layout view, then form view it will show on the monitor I have Access up on - but if I go back to Design and then form it will disappear again.
If I move Access to the left monitor (in this example). open the form in design and then form view it pops up visible. Seems clear that it ties the opening position of the popup to the monitor Access was opened on.
I have a footer in my Report that contains subtotals. I'd like to add a textbox that says "Total" to my report, and although this label shows up when I view the report, it disappears if I export to Excel. How to prevent the label from disappearing when I export? (Attached a screenshot of my report's design.)
Is there an efficient way to have default text in a textbox on my form (like a search box), but have it so that when a user focuses on the textbox (to type in a search term), the word should disappear.
Just like the search box on windows 7 start menu.
Then, is there a way to have the default text not be the actual text in the box? Otherwise, my search box will search the default text.
My database is replicated. When new fields are added to a table that is tagged as replicable, the field is there but not visible. (there are other fields that are visible) This affects queries based on the new fields as they do not show up in the query.
However, when I make the table non-replicable, I see all my previously hidden fields and of course my queries, query-based reports work fine.
Any suggestions? I've already run the compact & repair utility with no improvements.
I am designing an application in Access 2003 and it's pretty much working now.
I have a main form set to automatically open (through database startup).
sometimes, access disappears before even showing the form. The ldb file remains in the folder and access does not show up in Tasks (so it's really terminated).
I do have On_Load event code in the form, but nothing drastic and I went through and made sure that all subs have Error Handlers.
It doesn't give me any error messages, just disappears. This also happens when I try opening the main form from the open Access app, but not as often.
Usually, if I open by holding Shift, enter the form in Design view and close it, it then opens no problem.
Any ideas? I searched and googled, but to no avail.
I have a problem with my database. When I'm writing in my data to the database via the forms, the data isn't in the correct order when I start the database up again. Its mixed up. I am using 6 tables and combining data. Why? I have no relations in the actual tables, but some relations like queries. What should I do? I am going crazy...hehe
I'm totally newbie so please go easy on me, I finally learnt how to make database and tables by reading online tutorials. I'm now able to create a simple database in access, using tables and stuff. I want to ask, say i put 500 entries in my database and then i want to search for some specific data, how do i do that?
Say I'm making a database of model's agencies, and i want to pull all the models working in same agency and stuff like that, how do i do that. Do i have to create queries or forms? i'm confused, please help.
Is it possible to take data with all the same fields and put it into another database with the exact same fields, etc. ??? Thanks for your help in advance!!
Dear all, I have a lot of data already in my database and i need to validate to ensure that Various fields are populated correctly. how can i do this please?
Help! I have creadted a basic foirm to collect data. The problem is when I switch to form view, the "add data, delete data" butons are disabled. I tab thorough the fields and I cannot add any data??????????????
I have checked and rechecked the data properties of the form tab and allow additions and allow deletions are set to yes.
I am at the end of the rope and I am completely lost. Do I have to reinstall Access?
In my employee attendance database each record contains an employee id#, a number corresponding to an attendance infraction, and a corresponding date. Each week a clerk queries the database to pull up records for all employees who have a yearly 'total number' of infraction values over a certain numerical limit. Any suggestions as to what is the best way to: 1 - Calculate that yearly 'total number' for every employee. 2 - Retrieve the date of the most recent attendance infraction for each employee that has a total value that is over the limit?
I want to use one database to remove data from another e.g. use a list of telephone numbers to remove remove any entry with one of those telephone numbers in another database.
Greetings.. I am attempting to teach myself Access but I am having trouble trying to figure out how to implement into my database the features I need my enduser to have.
Basically I am trying to create a Contacts database and I want the "form" that my end user enters data into to look like this (all examples are minimalist because I am looking for specific logical answers to solving my problems, not field suggestions):
Name: [textbox] Phone: [textbox] Email: [textbox]
Now I need to have Phone and Email each in their own tables, or in some way seperated so that I can retrieve a list of emails or phones without duplicates.
My initial attempt was to create the tables:
*key
NAMEtable *Name Phone (related to PHONEtable.Phone) Email (related to EMAILtable.Email)
PHONEtable *Phone
EMAILtable *Email
Now the problem with that setup is that I cannot create a new entry into NAMEtable unless the specific Phone AND/OR Email are ALREADY listed inside their respective tables.
What I need to be able to do is give my user the ability to use a "form" with 3 fields, that will do the following:
Allow a new Name without a Phone or Email. Allow a new Name WITH a Phone and/or an Email. Allow a 2 different Names to have the same Email, but pointing to only 1 instance of that email in a different table. (which it already does all if the Email and Phone already exist in their tables, I need it to be able to create new entrys into those tables from the form if they don't already exist). I also need to be able to enter in new Emails from a different form2 that dont have any relation to any persons in Name (which isn't a problem either at the moment since I just open the Email table and enter data).
I am unsure how to execute this first forms dilema though, I am assuming my database design is fine and it has something to do with some formality or code that I am missing to be able to do what I want. If so could someone help me out and make some suggestions? Or if my design is way off and there is a better way to be able to enter and store a Name, Email, and Phone from a form that relates back to a single contact and prevents duplicates then please let me know. Thanks
Also here is another post I made in a different forum but wasn't able to get quite the answers or help I was looking for.
"Name [textbox] Email [textbox]
if the email the user is entering isnt already in the database it will not create a new row in email.email and then link contacts.email to that specific email.email...........
for example if
contacts NAME , EMAIL row1 = bob , email.email row1 row2 = bobswife, email.email row1
email Email row1 = bob@bob.com
then I open a form that will have 2 members, both pointing to the same email.
1 of 2 Name: [bob] Email: [bob@bob.com]
2 of 2 Name: [bobswife] Email: [bob@bob.com]
Ok, lets say I go to create a new member
I can do this just fine:
Try1 3 of 3 Name: [bobsson] Email: [bob@bob.com]
What I can't do but need to be able to do is:
Try2 3 of 3 Name: [bobsson] Email: [bobson@bob.com]
It will return an error stating that the email isnt in the email table. I need to know how to get Try2 to do what I want so that when I enter 3 of 3 my database looks like this:
