Subforms Stop Working After Adding Table To Query
Aug 16, 2013
I have a form with sub-forms. All the subforms work smoothly, I am able to assign students to classes, enter their late/absence data etc... But there's 1 subform that I want to include the Gender (male,female) so I have to go back to the query (see attached picture) to include the table with gender data and map it with other tables, then insert a combo box in that subform, which display the gender of the student along side with other data.
But the problem is as soon as I modify the query for that subform, all the subforms (including that one) stop working, I'm not able to enter and change anything with error like Recordset is Noteditable ( see attached screenshot).
And after I remove that table from the query and the combo box from the subform every works just fine again.
View Replies
ADVERTISEMENT
Sep 19, 2013
I have a form that allows a user to complete a stock take. I would like to stop other users from receiving or despatching stock while a stock take is in progress.
Is there a way I can lock a table, or stop users adding or altering records that match certain criteria. i.e. don't let users receive or despatch stock from with a locationID of 'A'.
View 4 Replies
View Related
Aug 6, 2014
I have a ACCESS application that I wrote several years ago and use quite a bit. Sometimes, I will open it up and it does not work properly. I have a form that contains several subforms and one of the subforms has cells that contain formulas like this one
=[fExpense_entries].[Form]![total_form_expenses]
Normally, I see numbers in these cells. But sometimes, these cells are just blank. Sometimes, I can get the db to work again if I open and close it a few times. Other times, that doesn't work and I have to "save as" a new file name. Today even that didn't get normal operation back but I did a full system reboot and then it worked again.
I also sometimes get a message that my file has been opened as read only, and sometimes I get a message that macros are not enabled.
There does not seem to be any pattern to when it stops working or what it takes to fix it.
View 7 Replies
View Related
Dec 1, 2005
Hello all,
I have combo boxes populated with all values in a particular field, then shunt the form onto that record (standard combo box for selecting records from a list in other words).
Problem is, these boxes seem to stop working if I leave the form for another and then return to it.
Here's the code Access puts behind:
-----------
Set SerialRS = Me.Recordset.Clone
SerialRS.FindFirst "[Serial Number] = '" & Me![Combo60] & "'"
If Not SerialRS.EOF Then Me.Bookmark = SerialRS.Bookmark
-----------
The form may be opened and closed via other processes (although never unloaded), but when I come back to it these combo boxes always stop working! Why?
View 3 Replies
View Related
Mar 18, 2015
With a many to many relationship using a junction table, do I have to manually plug in the ID numbers from the related tables in the junction table for it to recognize the related data? It sounds silly and obvious I guess that you would have to, but when I go to create a form based on a many to many relationship, I would like the ability to enter data without having to enter the related ID numbers in multiple places on the form or subforms to make it work. I guess I envisioned access using auto numbers to automatically update the junction when I wanted to add data related between the "main" table and the distant table joined with a junction. Can this be fixed using a Parent/Child relationship set-up in some fashion?
View 1 Replies
View Related
Apr 2, 2008
Hi there guys,
Im in a bit of a predicament, I have a main form with 3 subforms named:
Debit/Cred income for day subform
Cheque income for day subform
PDC income for day subform
and in each of these subforms, I have a field/text box (From a query) which is called "SumOfTotal Cost"
and also in the mainform I have another field/text box called "text73"
Now, all I wanted to simply do was to add all of those fields mentioned together to display them on the main form in another text box as a total so I tried to use this expression in the control source:
=Me!Text73+Me![Debit/Cred income for day subform].Form![SumOfTotal Cost]+Me![Cheque income for day subform].Form![SumOfTotal Cost]+Me![PDC income for day subform].Form![SumOfTotal Cost]
But when i open the form, the text box just appears saying
"#Name?"
I then tried to put brackets at the ends of the code, but still nothing would work, I then tried to access each individual "SumOfTotal Cost" by putting the following in a text box:
=Forms![Cheque Income For Day subform]![SumOfTotal Cost]
but it still comes up with "#Name?".
I am pretty annoyed and I would really appreciate it if someone could please help me
Thanks a lot
View 14 Replies
View Related
May 16, 2014
How to keep Access from including the detail line items in my report total?
Below is a quick image of part of the report.
The report is compiled from two tables. The first table has detail information on each line, in four columns:
State Name Amount Quantity (Not used)
AL Store 1 313.94 60
AL Store 2 12.59 1
AZ Store 3 576.45 90
CO Store 4 172.22 24
CO Store 5 502.25 88
The 2nd table is a summary by state with three columns:
State Amount Quantity (Not used)
AL 326.53 61
AZ 576.45 90
CO 674.47 112
I'm trying to make the report group by state, then name, amount and a subtotal for each state. This all works fine in the report.
I want to also add the amount field from the 2nd table (State Subtotals) to the report just for reference. I've managed to make this work as well. (See State Total on image)
The problem that I'm having is that when I try to do a final total of the State Total column in the report footer, it seems to add the (State Subtotals) amount field on every detail line (See red numbers in picture. they are not actually on the report, but Access thinks they are and includes them in the final total).
So far I've tried different joins, hiding various fields, sub reports, ect. I just can't figure out how to stop Access from calculating the detail lines.
View 2 Replies
View Related
Dec 5, 2005
I am creating an Access database that I want to be able to use to change a header table and its associated detail table. I have created a fmain-form that shows the header table fields. This form also has a subform which shows the detail records which are linked to a field in the header table. One header may have many linked detail records from the detail table. I have been able to easily enter information into the detail records that are shown in the subform.
I would like to know how I can add new detail records directly on the subform so that they will automatically pick up the correct header field from the main form. This way I know that the record I add to the sub-form with be linked to the open record of the main form.
Also, I would like to learn a way to sum all of the values of one of the fields shown in the sub-form and place it on the main-form. How can I do this so that I only sum those values that are in the sub-form that are linked to the open man-form record... not sum all of the values from all of the records in the database?
Thank you in advance for your help.
Mike
View 1 Replies
View Related
Mar 2, 2015
I am new to access but I am familiar with SQL databases. I have two tables: one for project data and another for project leader data. These tables are linked by a third table that relates projectIDs to leaderIDs. I am currently working on making a form that will go through each project and display the people involved in each project in a subform. I pulling the data from the the leader table that matches the the projectID with a query.
I would like to be able to update the information in this subform directly (which I can currently do) and be able to add new project leaders to a project. What is the best way to add the new leader to this? If a person is already in the database how do I add them with the form without re-adding new information to the table?
View 3 Replies
View Related
Oct 10, 2014
I have a form with a tabbed area, each tab containing a subform. One of these subforms adds records to a table. Another subform shows the totals from that table.
But when I add records, the totals tab is not updated with the new quantities unless I close the form completely and go back in. I tried adding Me.Dirty = False to the subform that adds the records but that makes no difference.
It seems as though the 'totals' subform gets those values as the form is loaded and does not change, even when records are added and then that tab is selected.
How can I get the totals subform to show the updated totals?
View 5 Replies
View Related
Sep 18, 2013
My subforms are randomly adding blank records and one subform I would like to stay blank repopulates with data, though not necessarily the most recently added record. Both of these seem to happen when I navigate to other main records in the database and then return to this page.Using Access 2013, I have a large form with 10 pages. On one of the pages, I have two subforms. This is set up to gather many-to-one data. The top subform is my data entry form with three fields (two combo and a text) and a command button. The bottom subform is a datasheet displaying the three fields.
The two combo boxes are cascading, and they work great. The text box is there to collect additional info for each selection.
The command button works to
1) save the record,
2) requery both subforms in order to display the new data on the datasheet,
3) clear the combo and text boxes, and 4) set focus back to the initial combo box. It all works!
But then when I leave that main record, the horror begins: blank records (from the "many" table) show up on the datasheet and the data entry fields do not stay blank. I suspect my problem is in the command button. I added this code to the OnClick for the command button:
Code:
Private Sub addMinistryItems_Click()
'save record
If Me.Dirty Then Me.Dirty = False
'requery both subforms
Forms![BCD MAIN 2013]!Child572.Form.Requery
Forms![BCD MAIN 2013]!Ministries1.Form.Requery
[code]....
View 1 Replies
View Related
Aug 5, 2013
I created a database a while ago which has been working just fine, until I wanted to make one addition: an attachement field to one of its tables.
If I do so, some queries don't work anymore and as a result of that, some (sub)forms do not work. The only error I get is: The search key was not found in any record.
When I add an other kind of field (like a text field), the problem does not occur..
When I remove the attachment field again, everything works fine again...
View 8 Replies
View Related
Mar 8, 2006
I've set up a query to calculate the difference between 2 time fields. I set the property to short time.
The thing is - when I run the query in a Select query, the times in the column which contains the difference, appear correctly : ie hh:mm, but when I run the same query in as a make table query, the time difference field (although I have set the property to short time) appear something like this:
0.664464564646
Can somebody please tell me how to make it that the time difference results field in my make table query appears as hh:mm??
Thanks in advance - nelld
View 1 Replies
View Related
Jun 19, 2012
I seem to be having a problem with access 2007
When I use the filter by form, filter by selection, and toggle filter buttons located on the ribbon, they work normally on my form, in the usual way.
However when I use the button wizard to add the various available filter buttons to my form, it adds the buttons with the correct graphics on - but in form view clicking on the buttons does not actually do anything.
I've tried running compact and repair but still the same.
View 5 Replies
View Related
Jan 30, 2007
I have data recorded using an input mask,
"RF">L-0000;;*
to display data in the style: RFA-0001, RFA-0002 etc.
I have a make table query to join this field to another, to create a combo box look up.
Unfortunately, after I run the query, the only data from the input mask that gets imported is A0001, A0002 etc.
What have I done wrong? I guess the error must be in my specification for the input mask...
Any and all help gratefully received.
View 4 Replies
View Related
Nov 12, 2014
I have a running total query that seems to run but when I try to total the query results then Access will be "Not Responding". I tried to change it to a Make Table query because I need to use the running total result in another query. So I created a table but when I try to run the make table query it just says "Run Query" at the bottom. Here is the query:
SELECT [OTMissing].[Employee], [OTMissing].[AsOf], [OTMissing].[HRsEarn], (SELECT Sum(OT1.[HRsEarn]) FROM [OTMissing] As OT1
WHERE OT1.[Employee]=[OTMissing].[Employee] AND OT1.[AsOf] <=[OTMissing].[AsOf]) AS RunningTotal, [OTMissing].[RemainPP] INTO OTGenerated
FROM [OTMissing]
ORDER BY [OTMissing].[Employee], [OTMissing].AsOf;
My OTMissing query is 47061 rows. Does that have something to do with it? The only other thing it might be is that most of the records have 0 although I'm not sure why it would be a problem I thought I would at least mention it.
View 2 Replies
View Related
Aug 7, 2013
I have a database in Microsoft access 2010. The database has a table that stores prospective customer records, and a form that is used to input a search criteria(s) via textboxes, which then queries prospective customers table and returns the records that contain the inputted search criteria(s).
An Example of Textboxes values on the search criteria form:
Textbox - name: bob
Textbox - address: Left blank
Textbox - category: car,boat,truck
I Have tried creating a query with the following
field: name
criteria: like “*” & name & “*”
field: address
criteria: like “*” & address & “*”
field: category
criteria: like “*” & category & “*”
SQL code:
SELECT customerName ,address,category
FROM prospectiveCustomers
WHERE customerName LIKE “*” & name & “*” AND address LIKE “*” & address& “*” AND category LIKE “*” & category& “*”;
That works, but only for one value in a textbox. Once there is more than one value in a textbox (e.g name: bob,smith), the query returns no records.
I have also tried splitting the values using the comma as a delimmter, then inserting the values into a new table. That is fine until one of the search criteria textboxes has been left blank. So the query I created will run, but returns no records.
SQL CODE:
SELECT prospectiveCustomers.name, prospectiveCustomers.address,prospectiveCustomers. category
FROM prospectiveCustomers, [SearchCriteria-name], [SearchCriteria-address],[SearchCriteria-category]
WHERE prospectiveCustomers.name Like [SearchCriteria-name].name AND prospectiveCustomers.address LIKE [SearchCriteria-address].address AND prospectiveCustomers.category LIKE [SearchCriteria-address].category;
View 2 Replies
View Related
Dec 23, 2013
Why is this update query not working, I'm trying to update a date field stored in a table.
The new date is passed from a txtbox on a form to the update query!!!
SQL code
UPDATE TblDietPlantemp SET TblDietPlantemp.MealDate = [Forms]![FrmSwitchBoard]![txtCusDate];
View 3 Replies
View Related
Nov 23, 2005
Hi All,
I am trying to add the infomation from a query and place it in a table so that I can use that data to build a Union Query that plots onto a chart.
Heres what I have:
Dim qDef As DAO.QueryDef
Dim SQL As String
Set qDef = CurrentDb.QueryDefs("QryBaseline")
SELECT tblAudio.AudioID, tblAudio.PatientID, tblAudio.AudioDate, tblAudio.Type, tblAudio.L500, tblAudio.L1k, tblAudio.L2K, tblAudio.L3K, tblAudio.L4K, tblAudio.L6K, tblAudio.L8K, tblAudio.R500, tblAudio.R1k, tblAudio.R2K, tblAudio.R3K, tblAudio.R4K, tblAudio.R6K, tblAudio.R8K
FROM tblAudio
GROUP BY tblAudio.AudioID, tblAudio.PatientID, tblAudio.AudioDate, tblAudio.Type, tblAudio.L500, tblAudio.L1k, tblAudio.L2K, tblAudio.L3K, tblAudio.L4K, tblAudio.L6K, tblAudio.L8K, tblAudio.R500, tblAudio.R1k, tblAudio.R2K, tblAudio.R3K, tblAudio.R4K, tblAudio.R6K, tblAudio.R8K
HAVING (((tblAudio.Type)="BL")) AND (((tblAudio.PatientID)=[Me]![PaiteintID]));
I have a "tblEvaluate" that I want to store the above selected record.
I am not sure whether to add and Delete, OR Edit and Update?
How do I get the above information into the table - "tblEvaluate"?
I know it's simple, I just have a mental block!
Thanks for you help....ENVIVA
View 3 Replies
View Related
Mar 15, 2006
Hello folks first post here. I tried reading through the boards to find someone with a similiar question, but not luck yet. I have been working with a basics of Access for about a year. I did take take a bit of programming in college but I don't remember much of it, so I looking for a little help. I can usually Google for answers but I cannot seem to find the write key words this time.
A while back I created a table that tracks employees' data and recently I started working with the insurance data I have gathering for sometime.
It goes something like
Field Name Data Type
SS# Number (primary key)
Insurance $ Number
PPO YES/NO
HMO YES/NO
Self YES/NO
Spouse YESNO
Children YES/NO
Family YES/NO
Dental Self YES/NO
Dental Spouse.......etc.
What I'm trying to do is turn the yes/no's into total dollars for each person. I have done this in Excel but the fields were differnet and I had to use a many, many if.'s or a lookup table(data array)
PPO doesn't = a dollar amount by itself, but PPO + Self = $19.03 or PPO + Self + Dental Self = $23.00. Or the person can just have Dental Spouse = 8.86. Pretty much the people can take a piece and leave another.
"Insurance $" is another field name and is where I am trying to get the total to go.
I was thinking of a lookup table but I don't know how to do that in outside of Excel.
I am thankful for any help or even a nudge in the right direction.
~Profector
View 4 Replies
View Related
Apr 4, 2005
My form is based on a select query. There are 2 combos that filter the data displayed in the form by status and property. All of the data is contained in the same table and the query is looking at just the one table. Clicking a button opens a listbox that contains all of the filtered data and clicking a record in the listbox populates the form with that record and closes the listbox. It works good like this.
When I try to add one field (labor) from a second table to the query with that same field added to my form, as long as there is a value in the labor field in the table then it works. If there is no value for labor then the form returns no records. There are records there but they do not have labor assigned yet. I set a default value for the field on my form - (not assigned yet). The listbox displays the records even though there is nothing in the labor field, but clicking a record in the listbox returns an error of 'no current record'.
Actually I dont even have to add the labor field to the form, just having it in the query mucks up the form. I must need an isnotnull statement in the criteria or something.......
Any ideas what I may be doing wrong?
Thanks!
View 6 Replies
View Related
Mar 5, 2015
How can I stop rounding of a number when it is written to a table ? The "Data Type" is "Number" and defined as "Long Integer", "Standard", and "Auto". The number appears with two (2) decimal places on the form, but when inserted into the table (using the SQL "INSERT" statement), it's rounded up (i.e. 34.75 to 35.00).
View 4 Replies
View Related
Dec 27, 2005
I have a make table query created from a linked to a table. i want to add field to the make table query that are not fields on the table. How do i insert a created field? Hopefully this is a basic question.....
View 1 Replies
View Related
Oct 4, 2007
If I have a make table query where I want to add in some blank fields, say: Field1: , Field2: , etc. is there a way I can make these fields a yes/no type instead of the default text?
I know I can manually go into the table in design view after I run the query, but I was hoping I could do it beforehand. :)
Thanks!
View 1 Replies
View Related
Apr 3, 2013
I have a database that lists all the books I have read. The Table "Books" holds "AuthorId' "Title" etc. I have another table that is called "Whats Next To Read" which I store the next book that I will read from that author. It also uses "AuthorId" and "Title" etc. In the form, how can I look to see if the title put in for that author in "Whats Next" does not have the same title already in "Books" table. I can use Dcount to find the title but it could be same title different author. I need same title with same author.
This is what I have so far.
If DCount("Title", "Books", "Title = '" & forms!Whatsnexttoread!Title & "' > 0 Then
Msgbox "Title is already in books.", vbInformation
End If
I need it to only look at the same author.
View 3 Replies
View Related
Mar 14, 2008
I've got a fields called rev code that contain the following values:
field name: 110 131 250 255 258
field value: 7.49 6 11.25 12.11 78
I'm writing a query that pulls from the first two digits of the rev code and need to round off to the nearest dollar so in my query I'll have a column 11 with a value of 7, a column 13 with a value of 6, then I need to take columns 250 255 and 255 add the values together and round off so I get a column 25 with a value of 101.
How do I do that?
View 14 Replies
View Related
