Dates Not Updating Subform RecordSource
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 Replies
ADVERTISEMENT
May 30, 2005
Hi everybody,
I have a form with its tabbed subforms' recordsource determined by the code:
Private Sub childTvl_Enter()
Me.childTvl.Form.RecordSource = "SELECT * FROM tblTvlInput WHERE tblTvlInput.Dept = """ & Forms.frmBudget.txtDept & """"
End Sub
This is to filter the huge data by department and only allowing the specified department to view their code. However, I find it slow and pesky as it only shows the required information when the user clicks on the child. I have tried putting the code above elsewhere but to no avail.
Is there a better way of speeding the filter as well as showing the user his own records when he clicks on the tab?
Thanks in advance. =)
You guys here are saviours!
View 3 Replies
View Related
Sep 4, 2006
I have a form with 5 tabs. Each tab has 2 subforms.
When the form loads I am trying to select it's recordsource by doing this:
Me!Subformname_A.RecordSource = "SELECT FieldA, FieldB FROM TableA WHERE"
However, it throws an error.
Is there something wrong with this ?
View 1 Replies
View Related
Apr 18, 2005
Weird yet aggravating problem.
I am trying to setup a routine that will allow the user to change the RecordSource of a subform. The RecordSource is being changed yet once I move to another record the subform is reverting back to the original subform RecordSource. I am using the below command to change the subforms RecordSource...
Me.MySubformName.Form.RecordSource = MySQL
I even to make a copy of the subform and I changed the table it is linked to yet I got the same result
Me.MySubformName.SourceObject = "MyOtherSubForm"
There are no functions in the record selectors that would interfere with the subforms RecordSource.
Any suggestions as to why the RecordSource of my subform will not "stick" when I move to another record?
Thanks in advance for your help!
View 8 Replies
View Related
Jun 29, 2005
Hi
I have problems working with a 3 level form
I have one main form called Projects
Depending on the record on the form Projects, the subform called Products will display the correct info
And on the OnCurrent event of the form Products, it will display the correct info for another subform called Product_Info
The problem is, the subform called Product_Info is dependant on a field on the Projects form, and on a field on the Products form so I cannot directly link the Product_Info form with either the Projects of Products form
What I'm doing is changing the recordsource of the Product_Info subform on the OnCurrent event of the Products subform but for some reason it won't work
If I display the recordsource of the Product_Info subform, it displays the right recordsource triggered on the OnCurrent event of the Products subform, but the data doesn't display
Thanks
View 3 Replies
View Related
Mar 13, 2014
I know this is probably already in the forum but it is getting pretty big and I can't find the answer. I have this:
frmMain frmMainSub frmMainSubSub recordsource = query1
How do I change query1 to query2?
View 2 Replies
View Related
Mar 29, 2013
I have a combo box (cboManifestNumber) that is based on the following table:
tblManifestData
ManifestDataIDPK (autonumber PK)
ManifestNumber
RemovedDate
ManifestComments
TsdfIDFK (FK frm tblTSDF)
This table is related to:
tblTSDF
TsdfIDPK (autonumber PK)
I need to be able to update tblManifestData with a new manifest number and manifest comments, along with assigning it a TSDF. how to be able to enter a new manifest number and the associated data without having it create two lines in tblManifestData. I thought that I could enter a new manifest number, then requery the table and form so it shows the complete list of manifest numbers (including the recently entered one) while staying on the newest entry.
View 2 Replies
View Related
Apr 7, 2005
Hello there
I have got a 2 problems:
1) i am creating a booking form with 'arrivaldate' , 'departuredate' , 'roomnum' and i need an error screen to come up if the roomnum has already been booked in between the arrival and departure dates which will be on the bookings table
2) i also need to work out how much this costs automatically in 'amountdue' i can get the date difference and times it by the cost but it does not store this into the table
Thank you for reading this and your help will be most greatful
Carl
P.S i am very new to access :o sorry :o
View 8 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
Mar 19, 2013
I have a table which is used to store info regarding medicines dispensed. I also have a corresponding Form to enter data. The fields in Table are
ID- number
Dispensed Date- date with dd/mm/yyyy format
Dispensed Type- text
Quantity- no. of days
Next Collection Date- date with dd/mm/yyyy format
The Form also contains same fields but it has a calculated field for ( Next Collection Date) where i calculate date using Dateadd function. Also the form has a Datasheet view. So records are added when I press Tab or Enter at last field.
Now the problem is the calculated dates arent getting updated in the table. And this is a huge problem as i have to run a query later where i will put a criteria on Next Collection Date.
View 3 Replies
View Related
Nov 6, 2013
So I currently have a table that tracks medication activity for patients called tblMedRanges. The Fields are the ID, StartMedication, EndMedication, Med ID/Medication Name.
In the End of Medication, some patients are not currently active so they have a set date/time field property. What I have for right now is the default value is set to
Code:
=date()
So that when staff adds a new medication it is set to the current date. These patients are supposed to be active, but it doesn't update the default value. I want it to continue to update to the current date every time the database is open.
I have three options that I know of(or think I know of) to update but can't commit to one in particular :
1)I want these individual records in the field to update the current time for today and was considering using some kind of data macro code using before update.
2)The other idea I had was splitting up the field into an EndOfMedication field that would display a value only if their inactive, and an Active field (text, value list active/inactive choices) that would be used in a query to generate the current date as med field. This would take some crafty query work with comparisons later on to generate list of drugs that they were on for each visit.
3) set to text field and convert the field to date field for comparison in queries.
View 2 Replies
View Related
Mar 28, 2014
I have a query that i use to update dates relating to jobs and at what stage of development they are in. It worked fine until I started to pull in some data (Prism) to update the user when the project is planned in to sample and when mass production starts (the field this is displayed in is not enabled). when I've incorporated this it I have no longer been able to update any field and my query has turned read only.
SELECT dbo_NPD_Container.PENumber, dbo_NPD_Container.JobNumber, dbo_NPD_Container.ProjectTitle, dbo_NPD_Container.Status, dbo_NPD_Sales.InitialQuery, dbo_NPD_Sales.InitialQueryDate, dbo_NPD_Sales.SpecificationIssued, dbo_NPD_Sales.ApprovalRecived, dbo_NPD_Sales.TuDrawIssued, dbo_NPD_Sales.TuDeliveryDate, dbo_NPD_Sales.TrialRequestIssued, dbo_NPD_Sales.SampleDate,
[Code] .....
View 2 Replies
View Related
Dec 27, 2013
I am creating a database that tracks current projects for my team at work.
Some projects are only due once (e.g., mailed brochures due on 1/1/14) and some are due at scheduled intervals (e.g., status report due monthly, quarterly, etc.)
Ultimately, I'm hoping that my end result will allow us to click on a form and look at what everybody has due that day, in the next 7 days, and so forth.
View 3 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
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 1 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
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
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
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
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
