Subform Updating Table
Jul 13, 2005
Hi guys, wondering if you can help because no one I've talked to can so far..
I've got a form with Room information on, and on it a subform. The subform contains information from 2 tables (Items and Audit). Items is basically a list of all the different combinations of furniture item that can exsist (a damaged green chair, a large new desk etc) and as theres only about 70, and 1000's of bits of furniture I decided having common entries would be a better method to save on memory.
Right, the Audit table contains a Room# field and an ItemID field, obviously each furniture item within the Items table has a unique ItemID. The subform is created displaying the information from the Items table, so it tells you that its a damaged green chair rather than just giving you the number 3, and has the ItemID hidden, just for linking purposes.
This works fine for displaying information, but I can't make it so the user can add a new record. If a new furniture item needs to be added then they want to be able to type (new blue chair) and not the ItemID number, so the subform somehow needs to lookup the ItemID from the information typed in as a new record, and then write this ItemID into the Audit table, before refreshing the form. Obviously though at the minute, if anything is added to the subform, it just goes onto the bottom of the Items table, and hence repeats whats already there, which is silly.
I'm just not sure how to go about doing it and would appreciate any help or advice that can be offered. If I've not explained clearly enough please let me know.
Thanks!
View Replies
ADVERTISEMENT
Jun 29, 2006
I have a subform in which I use the following statement =forms!frmetc.model It is picking up data that is listed in the form that it is a child of. It picks up the data just fine and shows it properly, but the fields in the table associated with it remain blank. Any suggestions of the right was to accomplish this task? Thank you!
View 1 Replies
View Related
Oct 19, 2005
I have a form to enter records in a table of staff attending training courses, in the form is a sub-form based on a query from a different table. When a staff number is entered on the main form the staff name grade etc appears on the sub-form, my problem is that I need the table to be updated from the main form and the sub-form. Is there a way of doing this?
Thanks
View 1 Replies
View Related
Dec 10, 2012
I have a form that users enter billing logs in. There is a button users can click to the right of their billing data that opens another form where they can enter details regarding the billing log. Beside this button is a check box that shows them if details for this particular billing log has been created.
I can not for the life of me work out how to get the check box to update with a check after I enter Billing details without opening and closing the form again. I felt I have tried requery and refresh in all ways I have seen here, yet it is not working.
The main form is frmDslEnter and the billing log is entered in a subform from here called tblSessions subform. The checkbox on this subform is called chkProgressEntered, and it's control source is =Not IsNull([DSLIDNumber]). I tried putting the refresh on the Close button on the Billing Details page, on the Got Focus of the subform and the main form and haven't had any luck.
View 12 Replies
View Related
Feb 11, 2014
I am having trouble figuring out the method to automatically update some fields in SubForm from 2 other SubForms.I have attached 2 pics, the first GradeEntry1 shows what the tblTopic_Class_Grade form looks like after I manually enter everything into it. GradeEntry2 is what the form looks like when I fill out the Form starting at the top.
I'd like the tblTopics_Class_Grade form auto-populate the TrainingClassID (it currently does this), TopicClassID, StudentID, TrainingTopicID based off the entry from the above forms.My end goal is that I need to have a grade for each student on each training topic for each class. Like:
Class1-Student1-Topic1-GradeX
Class1-Student1-Topic2-GradeX
Class1-Student2-Topic1-GradeX
Class1-Student2-Topic1-GradeX
View 6 Replies
View Related
Dec 16, 2014
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click()
'Creates quote date and prints quote
Me.QuoteDate = Now()
Me.cbAgentID.Requery
DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID
End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
View 1 Replies
View Related
Mar 1, 2006
I have a customer subform on page 2 of my inspection log. A combo box at the top allows the user to lookup customers based on name. My problem is after I enter a new customer and part number into the database it shows up in my combo box but I cannot get my two underlying subforms to pull up any information on it unless I close out the form and re-open it. here is a copy of my database and the code I am using for my combo box after update event. You would think the “requery” action fro the subforms would allow me to pull new information with out closing and reopening the form.
Private Sub Combo4_AfterUpdate()
Me.QuerytblSpecTable_subform.Requery
Me.QuerytblPartNumbers_subform2.Requery
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[customer] = '" & Me![Combo4] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub
Sample included
View 5 Replies
View Related
Jun 11, 2005
Hello guru's, I've searched the complete forum for the answer but I just can't find it. I've got a main form with 2 comboboxes on it. The first one filters the second one. What I need now is the 2nd combobox to filter the SubForm. I've tried to add a subform with the wizard but then it shows the complete table instead of only the ones selected in the second combobox.
I've got 3 tables: 1 for classes, 1 for students and 1 for the attendance.
The 1st combobox selects the class, the second one selects the student.
The 3rd table is used to enter absences (date, time, teacher etc) and it should appear in the subform when the student is selected.
Can anyone please give me a little advice? Thanks heaps in advanced! Sebastian.
View 5 Replies
View Related
Mar 17, 2006
Hi,
First post, but based on a long look-around, I'd say that if I can't find a solution here - there probably isn't one! :D And it's probably a simple one as well ...
My problem is the following:
I have setup a SubForm with two dropdowns - the second requerying based on what's selected in the first. It works like a charm when I open the subform by itself, the values update and everything. However, when I place this subform into another form it all of a sudden fails to register, and instead the "Enter parameter values" message comes up, with the seemingly correct Forms!SubForm!Value note. When I type in the value that I've just selected from the dropdown, the second dropdown updates correctly.
I know I must be overlooking something, but I feel I have checked everything I can - especially seeing as it works fine when run by itself.
Thanks for any help or advice!
"5itus
View 3 Replies
View Related
Oct 10, 2004
I have a listbox that navigates my form to a specific record based on ClientID
I also have a subform. This form has a field Called clientID which I want to pull all records with the current ClientID.
I can't get my subform to query only the records with the current ClientID in the ClientID field. How can I do this?
View 3 Replies
View Related
Jul 25, 2006
Hello,
I have a dropdown menu which has a lisrt of dates (mmm yyyy), and when one is selected it updates 2 boxes with 01/mm/yyyy and 30 or 31/mmm/yyyy
Private Sub cmbDate_AfterUpdate()
Me.frmData.Form.RecordSource = "SELECT * FROM qry_NatxReg WHERE [Company Name]='" & cmbCompanyName & "' AND [Time of Attempt 1] BETWEEN #" & txtDateFrom & "# AND #" & txtDateTo & "#"
Me.frmData.Form.Requery
End Sub
When i choose June, it works fine and gets all the june dates from [Time Attempt 1]
But when i Choose July, it includes June.
I only have data for June and July as it's still in testing.
I can't wrap my head round it!!
View 12 Replies
View Related
Aug 14, 2006
Hi well baisically the title explains the dilemma... I have a combobox in which the user selects an item and this updates the subform depending on the selection. However when i make a selection i get the following error:
Rumtime error '2101':
The setting you entered isnt valid for this property.
I understand what its telling me, however i cant seemed to find within the code where i have gone wrong, so heres the code:
Private Sub Combo4_AfterUpdate()
UpdateSubform
End Sub
Private Sub UpdateSubform()
Dim strSQL As String
Dim varWhere As Variant
varWhere = Null
strSQL = "SELECT * FROM Sub_qry_CostCentre"
If (Me.Combo4 > 0) Then varWhere = " WHERE (CostCentre = " & Me.Combo4 & " )"
' MsgBox (strSQL & varWhere)
Me.Sub_frm_CostCentre.Form.RecordSource = strSQL & varWhere
Me.Sub_frm_CostCentre.Form.Requery
End Sub
Private Sub Form_Open(Cancel As Integer)
UpdateSubform
End Sub
Anyhelp will be much appreciated, i have been stuck on this issue for a number of hours and is becoming increasingly frustrating..
thanks
View 3 Replies
View Related
Oct 22, 2006
This may be a simple question, but I'm not sure what to even search for. I have a form with multiple subforms. All the subforms have related fields that are linked via relationships. But one subform has no fields in common with the parent form.
I need to be able to have the subform update itself whenever the record is changed on the parent form. I can't figure out which event procedure to use. It updates correctly if I assign my procedure to the form_click sub, and then click each time I want it updated, but that's a pain. I've tried on load, on data change, on activate, on data set change, and a couple others, but nothing works automatically.
How can I make the subform run a subroutine each time the parent form changes records?
View 4 Replies
View Related
Jan 12, 2007
I have a form that has a main for, a subform, and then another subform inside the 1st subform. The form works like this:
Conact info
--->Call info and notes
---> Orders worked
Each of those being nested as they appear. The problem is, the user usually jumps strait to putting the order number in "Orders worked" before entering in any notes or info... So no record exists in it's parent form to tie to. How can I have the database create a new record in its parent form when the user types in the subform?
Thanks!
View 1 Replies
View Related
Jul 6, 2007
I was wondering if what I'm trying to do in Access XP and 2003 is possible. I've looked at countless templates and samples and havn't seen anything like what I want to do to be able to construct it. I've tried manipulating queries, relationships different table joins but to no avail. I'm only a beginner in VBA and know little SQL, I would be truly gratefull if someone can please shed some light on the below.
I've been creating a preventitive fleet maintenance database in access and want to make some changes that will make the database more flexible.
What I have is a main form which has the following main fields from the tblWorkOrder; WorkOrderID, FleetID, StartDate, FinishDate, Odometer, and ServiceTypeID.
Within this I have a subform in datasheet view (tblServiceItems, fields; ServiceItemID, ServiceDescription, ServiceCompleted(checkbox)) and this lists all the service items that belong to the ServiceTypeID in the main form.
I have another table called tblServiceDetails and this has the fields WorkOrderID, ServiceItemID which join the above two tables.
My problem is that I can't get the subform to list all the records that are in the table tblServiceItems, it only shows each record if you go through and select it manually. What I want it to do is to show like a listbox and allow me to go through the list and check of those service items once they have been completed. On top of this I want all those service items for that service type to be recorded against that workorder (regardless of completed or not) so then when I create a report on a WorkOrder It will list all the service items showing the checkbox's of which services have been done.
I've tried to do this with a listbox, but It doesn't show the checkbox, only yes or no. I've also tried using the tblServiceItems as the subform but this only updated the table and didn't create a record in the table tblServiceDetails matching it with the workorder.
Any idea's please...
View 13 Replies
View Related
May 19, 2015
I currently have a query that a subform uses to display some results. In this case, there are 3 columns displaying the name of a sandwich, the ingredients, and the number of servings of each ingredient. It looks like this:
SandwichName IngredientName Servings
Accordian Lettuce 1
Accordian Ham 1
Accordian Cheddar 1
Accordian Mayonnaise 1
That is the current output to the subform. The SQL statement I use to generate this list is here:
SELECT [TBL_Sandwich].[SandwichName], [TBL_Ingredient].[IngredientName], [TBL_SandwichContent].[SandwichTotalServings]
FROM (TBL_Sandwich INNER JOIN TBL_SandwichContent ON TBL_Sandwich.SandwichID=TBL_SandwichContent.Sandwi chID) INNER JOIN TBL_Ingredient ON TBL_SandwichContent.IngredientID=TBL_Ingredient.In gredientID
WHERE TBL_Sandwich.SandwichID=6;
I have bolded the last item the 'SandwichID'. It is this number I need to change dynamically based on a button being pressed on the main form. Essentially they enter a new sandwich, put the ingredients in, save it io the database, and I save the servings for each item as 1. Now I'd like the new sandwich to be displayed in the subform using the new ID of the sandwich which was just created.
If I try to put this long SQL statement in the RecordSource property I am told it's too long for the property to handle so I can't set this property dynamically. how to get the subform to requery with the new ID number of the item I just generated. I can get the ID number via DLookup, that's easy, but setting the subform to use this is my problem.
View 8 Replies
View Related
Feb 12, 2014
I'm using Access 2010 and need to update a sub form in a form that is within a navigation form. So it goes:
Navigation Form > Main Form > Subform
How do I do this? I've attached the following to a click event of a cmd button but I just can't get it to work:
Forms!frmMain.NavigationSubform.Form.frmJobHeadSub Form.Requery
View 10 Replies
View Related
Dec 14, 2014
I have a table contains 5/6 column. now i need to filter the table on a form randomly.
Suppose I have 6 different combo box for 6 column. Now on the sub form the control shows the whole table. i this case i have clicked the 3rd combo box, then it will show the whole column in its list. if i select any one of it the table will show the common values below. then i will click the second combobox which will populate the present data shown into the subform table after the first filter.
After that i wish to filter the fifth column where the fifth combobox will populate the data after the second filtering.
By all means the active data into the column will ony populate into the combobox.
View 1 Replies
View Related
Feb 21, 2014
I have a basic DB for recording supplier records, you enter a supplier ID and it performs Dlookups to run and display query results on the same form. That part works fine.
I've added a subform and linked it to an existing query which returns the suppliers top selling lines and the query picks up the supplier from the ID field on the main form, however when I enter a new id the subform doesn't update with the results specific to that supplier.
So I think I need a way of getting the subform to re-run the query each time I update the supplier ID field, but I can't see an option for this in the subform properties.
View 2 Replies
View Related
Aug 6, 2005
I have a problem with a total on my main form (calculated from a subform) not updating immediately. The updated total only displays correctly after I’ve navigated to another record and then back again. I would like the totals to update immediately after I’ve entered the new data. Can anyone help? Here are the specifics…
My main form has a subform which displays a dynaset of records from a query. I’ve set up this subform so that I can enter a new record, which is then written back to the record source for the query. This is accomplished via a combo box from which the user selects a product, and an AfterUpdate event returns the price of that product to the TotalPrice control on the subform. In the footer of this subform is a calculated control (called AdExpenseSubTotal), which calculates the sum of the TotalPrice field from the query. The footer of this form is hidden, and I’ve set up a control on the main (TotalSpent) form which displays the value of the subform control AdExpenseSubTotal. This total seems to update immediately whenever I tab off the relevant field in the subform – so far so good, no problems here.
Now here’s where it gets tricky. My main form has another subform which is hidden. This subform returns a dynaset of records from another query which has the same record source as the query on the first subform. Like the first subform, this subform also has a hidden calculated control with an aggregate function (Sum), and a calculated control (VendorSpent) on the main form to display its value.
As I said before, when a enter a new record in the first (displayed) subform (via the combo box) and tab off it, the TotalSpent control on the main form updates immediately, but the VendorSpent control doesn’t. Obviously the new record I’ve added doesn’t immediately show up in the query dynaset of the second (hidden) subform from which the VendorSpent control on the main form gets its value. The VendorSpent control only displays the updated total when I navigate to the next record, and then navigate back again.
Is there some Event Procedure I need to add to the AfterUpdate property of the combo box on the first subform which immediately forces the second (hidden) subform to requery? I probably haven’t explained myself very clearly, but I can provide a copy of the file (it’s only 184 Kb) if that helps.
View 2 Replies
View Related
May 1, 2014
I m having a subform in datasheet view. whn i use a join query as the record source for the sub form, i am not able to add or update any record in the datasheet. Is there anyway to use a linked query as record source of the datasheet with editable property.
View 2 Replies
View Related
Mar 8, 2013
I have a Form named 'Opening_Screen' which consists of various objects - Combo Boxes, RadioButtons, Check Boxes which the user can select.There is a subform called 'Report' within the 'Opening_Screen'. The source object of the subform is a query (called 'QueryX').
There is a button called 'Generate Report' on the form which when clicked - an SQL must run on the backend, update the query which in turn should update the Subform 'Report'.For me, the QueryX gets updated, but the subform doesn't. When I manually switch the form to Design View and then back to Form View - the subform gets updated. I tried the same through the vba code instead, but it doesn't update the subform.
View 10 Replies
View Related
Jul 17, 2013
I have a mainform in access with a subform (continous form mode). The subform's source is a query that has a criteria parameter that gets the values from a combobox in mainform.(list of projects) Combobox name= cmbProject
It works fine for some records (old records), so when I migrate new data into the table the comboxbox display the new data but when I select the record to be dispayed on the subform, the record or data is not in the subform, so the record is in blank.
View 1 Replies
View Related
Oct 19, 2014
I have a sub form that adds and displays appointments related to an order. The form has two combo boxes, cboVenderTypeID and cboVenderID and a date picker. There is a 3rd combo box on the parent form called OrderNode. The contents the cboVenderID combo box are filtered by the selections made in cboVenderTypeID and VenderID comb boxes. This works fine and adds the appropriate appointment to tblAppoints. The problem is when I try to add an appointment with of a different type (different value in cboVenderTypeID) all the values in the existing appointments change to the latest value selected in cboVenderTypeID.
Private Sub cboVenderTypeID_AfterUpdate()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
If Me.cboVenderTypeID = 4 Then
[Code] ....
View 1 Replies
View Related
Aug 26, 2014
I have been having issues with a subform on the attached database - for some reason it is not always updating to show corresponding records.
What I am planning, this is effectively going to be an interactive learning portal which can test users as well. There will be learning material (not yet included) and a bank of questions with corresponding multiple-choice answers, only one of which is correct. Each time the main form is loaded (Cat1MainForm) it randomly selects and orders questions, then, via a sub-form, returns three potential answers. There will then be radio buttons with which the users can answer appropriately.
If you load up Cat1MainForm and scroll through various questions you will see that sometimes the answers appear, other times not. Sometimes on one record the answers are there, you navigate away and back, and they have disappeared, and vice-versa.
View 4 Replies
View Related
Nov 4, 2005
Hi,
Mainform and Subform are linked on field "barcode".
Barcode is the primary key for the table that Mainform is based on.
When user is entering a new record, I have the BeforeUpdate event of Mainform.barcode set to check to see if the barcode already exists in the table, and if so, to give the user some meaningful error message.
Works fine, except that the subform still updates to match the invalid, previously-used barcode. I want the subform to stay blank until the user has entered valid data in MainForm.
How do I accomplish this?
Thanks for any ideas.
View 11 Replies
View Related
