Hello. The problem I am having is related to two tables. I have a table called Void and a table called Panel. A panel can have multiple voids and a void is assigned to only panel (one-to-many). In the Panel table, I set an autoincrementing number(SprayPanelId) for the pK. In the Void table, I set an autoincrementing number (VoidId) for the pk. I place the SprayPanelId into the Void table as a foreign key (same name in the void table). The problem I have is in my forms. I created a Panel form with a button to open a subform for data entry on the voids related to the panel in a new window. Data can be entered and is properly saved in the Void table. I open the Void table in a datasheet view under the table section and see it is properly there. I can run queries where Void.SprayPanelId = Panel.SprayPanelId and it returns the correct void with the correct Panel. The problem becomes when I open my void subform, it no longer displays that record associated with that panel. however, the record is there in my void table. Any thoughts? Do I need a filter perhaps, run any sql commands? Thanks in advance, Kevin
Code: Private Sub Command26_Click() If Forms![test site]![prp test].Form.[A Right Answer] = -1 Then Forms![test site]![number correct] = Forms![test site]![number correct] + 1 End If DoCmd.FindNext End Sub
Then when clicked it checks a yes/no box to see if "A right Answer" is the correct yes. Then it should pop to the main form and take the number correct cell and add one to it. I am trying to get the record to go to the next record inside the sub-form but docmd.findnext seems to be wrong too.
I have a form "Training", with a combo box that holds employee names and a subform with all of their training records linked by "EmployeeID". Quite often lately, when the user selects their name from the combo and the code below runs, someone else's records appear in the subform. There doesn't seem to be any kind of pattern, ie. the previous employee on the list or the next, etc.
Private Sub Combo6_AfterUpdate() ' Find the record that matches the control.
I have the db split with both the FE and BE on the server and a batch file that runs from a link on our intranet. The table that holds all of the data is at 3,195 records and the db is just under 4MB. I compact/repair regularly.
There are 66 users - usually 3-4 concurrently.
Is it time to think about moving the BE to SQL or Oracle?
I have a subform that displays records from a query. Usually, there is no issue. Sometimes, though, the subform stays blank for no apparent reason.
Observations:
-Open form and select relevant information (subform stays blank)
-Open query and 1 record is present
-Go form layout view and turn "Data Entry" on, then back off
(The form appears to refresh when this setting is changed and the subform is displaying the 1 record properly now)
-Close form, re-open, and select the same information as before (subform, again, stays blank)
Again, the subform usually displays the records just fine. It's only sometimes that it has this blank issue even though records are present in its source query. I've tried refresh and requery macros on the form and subform, but that doesn't get the information to show up (though, switching "Data Entry" on and off does).
I have search filter form that when I click the search button is passing a value in OpenArgs to the Main Form on open event. The OpenArgs is changing the recordsource of the subform on the main form appropriately. However, the main form is referencing all of the records, not just the ones that are int eh subform recordsource. My link master and child fields are correct. There is a 1:N relationship between my mainform table and subform table.
For example, my my subform, the recordsource is diplaying 2 records where the Status = BLUE, however, the mainform (Projects) is showing ALL records even where the status isn't blue. So when I scroll through the mainform records, if the project isn't status = blue, the status subform shows a blank record. I only want the Projects where the status is blue.
I have a listbox on a form that looks up a table to to allow me to pick a stock location. The table only has 2 colums in it, the Primary Key Field (ID) and the Store location. When using this in a a form instead of getting the Stores Location, all I get is the ID. I have tried changing the Bound Volumn Value and the Column Count Fields, however no change.
I am in the beginning of setting up a database and have NOT messed with multiple tables. I just want to make sure that I am setting up the tables as well as the relationships correctly.Here is what I currently have, 4 tables and they are named. Employee, phone, radio and spotter. Employee table is where my primary key is located and I currently have a 1-to-many relationship between Employee and the others.
What I want to be able to do is to store information in the multiple tables from 1 form, which I don't think will be an issue and then at a later date be able to pull up information. EX.Thus being able to skip anything being put into the spotter table. Then at a later date be able to pull up only the select information I originally put it?
I have several tables that I am trying to get information from:
Clock Number Table with fields: Clock #, Name, Title, Dept, Term, HireDate, & TransDate.
Completed Training with fields: Clock #, Data Completed, Doc # & Rev Level
Linked Table, Controlled Documents with fields: Doc #, Title, Effective Date & Rev Level
Linked Table, Distribution Table with fields: Doc #, Distribution, Rev Level & Effective Date.
Some of the tables have more fields that those listed, but they do not pertain to this query.
I am trying to create queries that will provide me with the names of employees who have not been trained on Controlled Documents that have been distributed to their department.
So far, I have been able to determine if no one has been trained, but if even one person has been trained, they do not appear on my list. I would like to know the Clock # and name of those that have not been trained.
I have created 3 queries to get this far. Query 1 is the Clock # Table and the Completed Training Table joining the Clock #. Query 2 is the Controlled Document Table and the Distribution Table joining the Doc #, Rev Level. Query 3 takes these queries and joins Doc # and Rev Level. and pulls records where the Rev. Level and Doc # is Null.
I am having trouble with the below returning the correct number of records, and can't see why.
I have one table, tblDevice, which has 4 columns, ID | DeviceRecNo | ExcludeFromCheck | StockLocationID
ID - Autonumber DeviceRecNo - Number ExcludeFromCheck - Number (1 = yes, 0 - No) StockLocationID - Number
I have the following running as part of some code, but it is not returning the correct number of records, and I cant see why not. I have tried creating this in a query in Access itself, and copy the SQL into VBA and it still doesn't return the correct number records.
I have 4 records in there for testing, all with StockLocationID = 3, all with different DeviceRecNo, and two each of ExcludeFromCheck, 2x0 and 2x1
Code: Public Function test() Dim db As dao.Database Dim rs1 As dao.Recordset Set db = CurrentDb Set rs1 = db.OpenRecordset("SELECT DeviceRecNo FROM tblDevice WHERE StockLocationID = 3 AND ExcludeFromCheck = 'No'") Debug.Print rs1.RecordCount End Function
I'm sorry if this is obvious, but I have asked local professionals and scoured this site for answers before posting...
I have a database that I'm using to store and analyze language samples (basically, the mistakes that students make when trying to speak in another language). For each bit of language, I want to be able to say what domain the mistake belongs to - e.g. the 'clause', 'phrase', or 'word' domain. I have a table containing the five domains. I also have a table for each of these domains, containing the specific types of mistakes relevant to that domain. It is conceivable, but unlikely, that I will ever add to the domain table, but highly likely that I will add types of mistakes in the five related tables.
As I analyze bits of language, I will be appending each analysis (record) to a table as shown in the attached image. So far so good. The part that I cannot fathom, though, is how to display (let alone perform calculations on) this data: While the ErrorDomainID is a foreign key to the ErrorDomain table, the ErrorUnitID could refer to one of five tables, so that an ErrorUnitID of 2, for example, could refer to one thing if the Domain is 1 but quite another if the domain is 1 or 5 or whatever.
I've tried all kinds of coding and SQL workarounds, but I have a feeling I'm just missing something very basic here. If anyone could offer some guidance, I'd be extremely grateful!
I am still new at database design, and cant quite come to terms with my project and access way of doing things.
I have to keep a register of people who participate in projects. The projects can be of two different kinds. BUT (here comes the tricky part) The projects are being evaluated on three different indicators, with each one of these having 4 measurements, in the range of 4-0. That was a quick introduction. Now let me break it down in parts.
The people:
I have made a Uniqe identifier (Social Security number (PK))
First Name Last Name Department (This can be 4 different departsment) made a drop-down menu type.
The Projects:
Unique identifier (Project ID (PK)) Social Security number Project Type Start date End date
Project type:
Unique identifier (TypeID (PK) Project type (Cti / Regular)
How might i design this the best way, so i can combine the people with the projects there on. And which type.
There can only be one person, but he can be on many projects. These projects can vary in type. My problem is ensuring there connected proberly.
Furthermore, once the basic design is made i need to make evaluations based on their performance if they are on the projecttype "Cti".
here i need 3 x this:
Evaluations:
Objective (range 0-4) Baseline reading (range 0-4) Midway reading (range 0-4) End reading (0-4) Success = Yes/NO (here i will do a End reading <= Objective formula).
That was a rather long list, but i have sat working on this in three whole days, and im getting a little fed up with not knowing up-and-down.
we have Oracle 11g as main database and for viewing purpose, we have created an mdb (Access 2007) using Link tables via ODBC connection (system DSN).While querying, count queries are working fine with accurate record counts but, all select queries are not working fine. It filters and displays with wrong records. There is no issue in query building.how to get correct records through odbc connection to Oracle database.
So I have a table with around 20,000 records and there is a field which holds a phone number for each individual within the organization, I want to run a select query that counts how many are not in the correct format
The format is 10 digits and it must start with a 0
I'm having some trouble getting my information that I input on my form to store in my correct table. I will attach my DB so you can take a look at what I have thus far.
Here is what I am wanting to do:
I have TblEmployee, TblEquipment and TblJunction and FrmTracking and FrmUpdate
I input the bulk of my information thru FrmTracking, my trouble is I can not seem to get the information that I input in my FrmTracking to store in the correct table. I can get the information to store in TblEmployee, however the information that I want to be stored in TblEquipment will not store in there.
Table 1: contains staff names and contact numbers Table 2: contains training above staff have been on or need to go on Table 3: contains pc and printer asset numbers of above staff
I used a form and entered some new members of staff in table 1. They got their auto numbers etc but when I open table 2 and table 3 those new members are not showing up in those tables. I have checked the relationship status between the 3 tables and the staffID from Table 1 is associated to table 2 and to table 3.
What's stopping the new entries from showing up in tables 2 and 3 ?
This one is driving me NUTS! I have a form with a combobox, a few textboxes, and a sub-form. On Load the form is populated with a sql command/rcSet.Fields() results. The user then chooses a status from the combobox to narrow down the results. The combobox OnChange event looks like this... sDate = Forms("frm-MENU").txtS_Date.Value eDate = Forms("frm-MENU").txtE_Date.Value xJob = Forms("frm-HD/DVR_CC").lstJob.Value
On Error GoTo hd_dvrErr 'this gets the top of the HD/DVR form sSQL = "SELECT Count(*) AS Total, Sum(IIf([pmt_meth] In ('C','E'),0,1)) AS Error, Sum(IIf([pmt_meth]='C',1,0)) AS [Credit Card], Sum(IIf([pmt_meth]='E',1,0)) AS EFT " & _ "FROM [tbl_HD/DVR_CreditCard(*)] " & _ "WHERE ((([tbl_HD/DVR_CreditCard(*)].CDATE) Between #" & sDate & "# And #" & eDate & "#) AND (([tbl_HD/DVR_CreditCard(*)].JOB_TYPE) Like '" & xJob & "*'));"
Set db = CurrentDb() Set rcSet = db.OpenRecordset(sSQL)
With Forms("frm-HD/DVR_CC") .txtTotal.Value = rcSet.Fields(0) .txtError.Value = rcSet.Fields(1) .txtCC.Value = rcSet.Fields(2) .txtEFT.Value = rcSet.Fields(3) End With
'this gets the bottom or subform of the HD/DVR form sSQL = "SELECT IIf([Agent]='UNKNOWN','xxxxx',[REP]) AS [Agent ID], [tbl_HD/DVR_CreditCard(*)].Agent, [tbl_HD/DVR_CreditCard(*)].JOB_TYPE, Count(*) AS Total, Sum(IIf([pmt_meth] In ('C','E'),0,1)) AS Error, Sum(IIf([pmt_meth]='C',1,0)) AS [Credit Card], Sum(IIf([pmt_meth]='E',1,0)) AS EFT, Sum(IIf([pmt_meth] In ('C','E'),0,1))/Count(*) AS [Error Rate] " & _ "FROM [tbl_HD/DVR_CreditCard(*)] " & _ "WHERE ((([tbl_HD/DVR_CreditCard(*)].CDATE) Between #" & sDate & "# And #" & eDate & "#)) " & _ "GROUP BY IIf([Agent]='UNKNOWN','xxxxx',[REP]), [tbl_HD/DVR_CreditCard(*)].Agent, [tbl_HD/DVR_CreditCard(*)].JOB_TYPE " & _ "HAVING ((([tbl_HD/DVR_CreditCard(*)].JOB_TYPE) Like '" & xJob & "*')); "
Set qdTemp = db.QueryDefs("qry_HD/DVR") qdTemp.SQL = sSQL qdTemp.Close
If Not Forms("frm-HD/DVR_CC").FormFooter.Visible Then Forms("frm-HD/DVR_CC").FormFooter.Visible = True Else 'DoCmd.Close acForm, "frm-HD/DVR_CC(Footer)", acSaveNo End If DoCmd.OpenForm ("frm-HD/DVR_CC(Footer)"), acNormal, , , , acHidden Forms("frm-HD/DVR_CC(Footer)").Recalc Forms("frm-HD/DVR_CC").Refresh Forms("frm-HD/DVR_CC(Footer)").Refresh ' DoCmd.MoveSize Height:=Forms("frm-HD/DVR_CC(Footer)").WindowHeight + Forms("frm-HD/DVR_CC(Footer)").FormFooter.Height
I have used similar code on another form and everytime the search criteria changes the sub form updates to reflect such. I know I am missing something small; can somebody please point it out?:D I need to have the subform show the updated (choice from combo) criteria.
If this seems to cloudy, please let me know and I will try and revise
I have two forms. The first form has individual data to include the person's Social Security Number (SSN). I have another form (subform) that has other data to include the person's SSN. I added the subform to the form (linked the SSN). Unfortunately, only the SSN field appears in the subform. None of the other data in the subform appears in the subform.
I have a Subform that is inside a Subform that its only purpose is to display some calculations but for some reason it is blank. Here is a screenshot of what I am dealing with:
See that area under "Pump Calc" that is blank...it should have data in it:
Here is the query that drives that data:
And here is a copy of that subform running in form view:
I have a form that shows a subform at the bottom which displays all the corrsponding jobs so that an individual can make the current master form display the specific job information. My problem is I would like the subform list not to display the current master's job as one of the listed ones.
Example:
I have made an order for today and tomorrow. Im looking at the order for today and I see the list of all the jobs I did which is two on the subform. I would like the subform not to display today's job since I am already in it.
I tried using an IIF statement in a text box in the mainform branch header section to return the branch expense if subform branch = mainform branch.......When I run the report I get all of the Employee overhead but only the last record for Branch 2 branch expense displays.
My Database is really simple, it contains 1 table (called Querys) and a form called Dashboard.
In my main form (Dashboard) I have inserted a sub form which shows all the records in my table (called Querys) when I use my main form and add a record this sub table can then be refreshed and it shows all my records.
This part is fine,What I would like to do is be able to select a record in my sub form and it show the record data in my main form. This would really speed up the navigation process as the database gets bigger.
I am trying to show a certain subform based on whether or not a field is blank or null. This database is not owned by me--it's what we use to run queries and reports on the data on a centralized server for the library. It's not even in the same city--so changing the tables is not an option for me.
So, I have a subform (subform1) that is linked to the main form based on 2 fields (BibID and Item_Enum). The trouble is that the second field (Item_Enum) is sometimes blank, so the subform will not display. So I created another subform (subform2), which is a duplicate of the first one, but this time only linked on the first field (BibID). So, if that second field (Item_Enum) is blank, I want subform2 to display and subform1 to be hidden. If the second field (Item_Enum) is not blank, I want subform1 to display, and subform2 to be hidden.
I've tried putting the VBA code on different events for the form, but I can't get it to work consistently.
There are two problems, one of which I think I have fixed--but I would prefer a better solution. The VBA code doesn't run unless the form is newly loaded. I've tried placing the VBA code on different form events, but nothing works. I want the code to activate whenever a new record is displayed. The work around is that the form IS newly loaded every time the user scans a barcode for a new book. That works now, but if I need to change the way the user looks up new records, this might not work anymore.
The second problem is that the VBA code doesn't display subform1 when Item_Enum is blank. It doesn't display subform2 either, but that's the desired result.
Private Sub Form_Load() If Me.ITEM_ENUM = "*" Then Me.subform2.Visible = False Me.subform1.Visible = True Else Me.subform2.Visible = True Me.subform1.Visible = False End If End Sub
I've tried changing the condition to Me.Item_Enum = null, and changing the false/true results accordingly, but that didn't work either. In that case, only subform2 is visible, whether it should be or not.
In the Purchase Order details form a Supplier has to be chosen via a combo box. Based on that another combo box in the subform displays products only from this supplier (Products table is linked to supplier table).
I managed to let the subform combo show only relevant products using criteria referring to the main form combo box. Also other product data will show accordingly in text boxes. So far so good.
However when I close the form and reopen it the subform combo box is blank, other text boxes still show the right values. If I remove the filter criteria for the subform combo then all fields show all data correctly. (However the combo box is unfiltered again 8-/)
So somehow the 'criteria' prevents the combo to show the value that was previously chosen.
