Subform Blues - Data Entry Setting Itself To 'No' & Requerying 1 Subform From Another
Dec 5, 2006
2 Subform problems
I have a data entry subform that is only supposed to show an empty record ready to be populated, and a display records subform that is supposed to show all the records. The subforms are both on the same tab of a tab control on my main form.
Problem 1:
The data entry subform shows all the records rather than a blank record. Something on my main form is causing it to show the records when it should not. Any ideas? The Data Entry is set to Yes.
To try to isolate the problem, I created a new form and added the subform to it where it behaves properly:confused:
I then added Me.DataEntry = True to the form open to see if that would solve my problem but it still sets the data entry to no.
If I have the properties box open when in form view of my main form, I can set the data entry to Yes and it works fine until I move to the next record of the main form when it resets to no. Teraing my hair out here.:mad:
My final attempt was to search the entire project to see if there is a "DataEntry = False" somewhere but there isn't. What is setting this property? Any ideas where I should look?
Problem 2:
After entering data in the first subform (data entry form), I want to re-query the second subform but I just can't get the syntax right. I have wrestled with the "Syntax for subs" document downloaded from http://www.mvps.org/access/forms/frm0031.htm (Microsoft MVP site) but to no avail.
My main form is called fdlgPrjDetails, the data entry is via fsubPrjCommentsUsersDataEntry and the subform I wish to requery is fsubPrjCommentsUsers.
None of the attempts below worked giving a cannot find control error.
Private Sub Form_AfterUpdate()
On Error GoTo ErrHandler
Me.Requery
'Me!fsubPrjCommentsUsers.Requery
'Me!fsubPrjCommentsUsers.Form.RecordSource.Requery
'DoCmd.Requery ([fsubPrjCommentsUsers])
'DoCmd.Requery [fsubPrjCommentsUsers]
ExitHere:
Exit Sub
ErrHandler:
MsgBox Err.Number & " - " & Err.Description & Chr(13) _
& Chr(13) & "Error in fsubPrjCommentsUsersDataEntry: Err 003"
Resume ExitHere
End Sub
Any Ideas?
Both problems have me stumped so I'll be grateful to anybody with a scoobie on this.:)
View Replies
ADVERTISEMENT
Jun 22, 2007
Hi Folks
I have a query based on a table which gives a breakdown of sales by month, sale type and by cost centre (department).
I also have another query which summarises the sales by sale type and cost centre so that and is based on the first query.
the second query is a subform of the first. I tried the second query as an update query but couldn't figure out how to execute it in order to refresh the subform. I've also tried to requery the second query having recreated it as a select query but I'm not able to refresh the data.
The aim is to refresh the data in the subform as any of the underlying data is changed in the original form.
I'm ok with VBA and with macros so if anyone has any advise or ideas I'd appreciate it.
Many thanks
Andy
View 2 Replies
View Related
Jun 23, 2006
Greetings. I have a database that displays forecasts of our faculty needs. I now have 2 tables, Managers and classes.
The Managers are bound to the form, the Classes to the subform.
The subform changes every time I choose a new manager,
as planned. But the Subform keeps requerying itsself,
and I can't enter/modify data as a result.
View 7 Replies
View Related
Apr 21, 2015
Within my main form I have a combo box called "workgroup." Also in the main form is a subform called "sfrmSubmissionRecords," and within this subform is a combo box called "covermemo."
Each cover memo is assigned to a specific workgroup, so my intention is for each time a new workgroup is selected from the dropdown, the covermemo combo box in the subform becomes populated only with the covermemos associated with that workgroup. I'm almost finished except for the final step when I try to make the values regenerate...Access says that can't find the referenced form "sfrmSubmissionRecords"
This is the code that I'm using:
Private Sub cmbWorkgroup_AfterUpdate()
Forms![sfrmSubmissionRecords]![cmbCovermemo].Requery
End Sub
View 2 Replies
View Related
Apr 27, 2006
Hello all,
I'm looking for a little help on something that I'm sure is an easy problem. I'm building a form for patients (data from Table: Patients) that contains a subform containing lab values (Table: Lab Values). I'm new at using subforms so I'm in a little over my head.
Both Tables contain the patients name to link them together. When I create the subform, I don't want it to display the patient name, because it is already displayed in the header. But if I don't include that field, I can't link the two tables together. I'm using one of Microsoft's templates off their website (called Contact Manager Database) as a guide, and it has exactly what I want - a sub form that only displays the relevant data for each patient, but when you add a record the data in the table automatically includes the name of the person.
I just can't seem to figure out how to do this. I'm sure I've been very unclear describing this, and I'm sure this is an easy task, but if someone can give me a little direction I would greatly appreciate it.
Cavy
View 3 Replies
View Related
Mar 28, 2015
i've got a Form that contains Subform with an embedded Query that contains 2 tables only (Payments & Invoices) the Join properties between them show all records from payments and what matches it from Invoices where the joined fields are equal (Invoice no).. So, when i enter certain data in the main form the Subform show the results for it from Payments table and only one field needed from the Invoices table !!
The problem is.. i can't edit or add any data in the Subform results with the previous setting, but when i completely remove the Invoices table from the embedded Query then swift to the Form and it's Subform.. i become able to edit and add data in the Subform easily... !!
So, how to enable the edit/add in the Subform with the 2 tables in the embedded Query ?! Cause it's really needed to show that field from the Invoices table.
View 3 Replies
View Related
Mar 2, 2005
1st:
I have used the Lesban Rich Textbox Control. However, I face a problem with the control and cannot solve. The thing is that whenever I select an item from a combo box which loads a no record it refers to, it always ignore my first letter input. The second one will go just fine (e.g. when I enter ABC...., the control will ignore the A letter) Hope you get what i mean. I dunno if I made mistakes somewhere with the control. Does anyone have any idea that such thing should be done?
2nd:
In the same form as above one, I also designed 2 other subforms, however, it always raises error when I enter data while the main form contains nothing. I want to disable those 2 subforms when the main form record isn't filled out. I found a similar result in the forum, but it doesnot work with Rich Textbox Control's update event.
View 6 Replies
View Related
Oct 2, 2006
Hi all,
I apologize if I'm missing something terribly simple.
Here's the background: I have a set of 3 tables linked to a main table based on a field called SampleID. I then have one main form (SAMPLE) with the other 3 tables (INDIVIDUAL, LOCATION & ORGANISM) all as subforms within the main form.
everything seems to work well in terms of links and getting the data into the appropriate tables and retaining the SampleID link. However, during data entry using the tab key, I am unable to find an eloquent way to move from the main form to subform1, then from subform1 to subform2, from subform2 back to main etc. I have set the tab order, and used the afterupdate control to change the focus which works, however as soon as I move from subform1 to subform2, the data just entered disappears from subform1. The data makes it to the table without any problem, but just blanks out the form.
From what I can see, when the subform loses focus it automatically saves the edited record and inserts a new record, thereby blanking out the fields that were just entered. Is there a property that can be set so that a new record is not automatically entered when leaving a subform? Is there a method to do this through coding?
any help would be greatly appreciated!!
Thanks in advance.....
View 3 Replies
View Related
Jul 7, 2005
I have a main form where the user chooses a BoxID. There is a subform on this main form that has a relationship between BoxID on the main form and subform. The users are entering data in the subform and when they have entered 10 lines of info I have a message that tells them to start a new box (I could not work out how to do it any other way as I lack VB skills). The user closes the form or clicks add new record to create a new box. If I view the previous record there should only be 10 lines of information in the subform but there are 11 as it takes the blank line as a new record....
View 8 Replies
View Related
Feb 13, 2015
How do I display a more meaningful message instead of the cryptic error about having to enter data into blah blah blah. How can I trap that error and provide them a more meaningful message about entering data. I have tried the following;
Main form name frmPatientRecords
Sub form name DentalRecords Subform
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Nz(tblPatientDetails!recordid, 0) = 0 Then
MsgBox ("sorry. Please complete the main record entry")
Parent.SetFocus
End If
End Sub
View 14 Replies
View Related
Sep 19, 2012
I have a mainform set to data entry to add records to TABLE1. I inserted a subform that shows fields from TABLE2. I have a field in TABLE2, let's say TBL2FLD1(NUMBER). I want to add TBL1FLD2(NUMBER) from TABLE1 in the mainform to TBL2FLD1 and update said field in TABLE2.
My problem is:
(1) I get a #TYPE! error when I try to add the two fields, which I have tried many, many ways (adding them together in the Default Value; creating an unbound field to hold the calculation and then putting that field into the Default Value of TBL2FLD1; ETC), and
(2) I don't know how to get the subform to update TABLE2. It seems like the control gets stuck in the subform. Is there a way to accomplish this without coding it?
View 3 Replies
View Related
Aug 17, 2014
In my main form, there is this date entry selection and a subform with data on it.
How can I make the subform filter its data from date entry selection?
I have already created a relationships for my tables
I created a query for this subform (should I need to?)
It does not refresh my subform or I do not know what to do?
View 12 Replies
View Related
Apr 23, 2014
I have a form that request the User to enter a Parcel ID number (99-9a-99-99aaa-aaa-aaaa). The sub form asks for the subdivision number, block number, and lot number. The subdivision number, block number and lot number are normally part of the Parcel ID number but sometimes they can be different. I want the sub form to fill in the Subdivision number, block number, and lot number as a default but allow the user to change the value if needed. currently the sub form will only show what is entered into the table itself if a change is made on the main form the sub form does not reflect the change.
View 3 Replies
View Related
Jul 10, 2005
Hey Guys,
Maybe I’m a little daft (or a lot).
I have subforms with the “Data Entry” property set to “Yes” and they spontaneously reset themselves to “No”
Has anyone ever seen that before?
View 2 Replies
View Related
Jul 18, 2013
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.
View 10 Replies
View Related
Jan 17, 2008
Hi,
i have a subform within a form. when the form is loaded, i would like the subform to automatically be ready for a new record instead of shown the last record entered. i have tried various macros ( not VB ) but to no avail. the form shows the persons info and needs you to add a certain piece of info. this would be the only reason for the form.
can it be done?
NS
View 1 Replies
View Related
Apr 30, 2008
I’ve created two forms, Form_A (parent) and Form_B (child). Each form requires key-in data and performs its own independent calculations based-on various tables and queries. I would like to have Form_B pop-up (like a dialog box) into Form_A. It’s important that Form_B maintain its ability to receive key-in data. Also, a control button inside Form_A used to display Form_B would work.
Thanks in advance.
View 4 Replies
View Related
May 15, 2015
I am trying to set a value to a control in my subform (On Load) but it is not working.
FRM_ASQ has a button upon clicking open a FRM_Planchange_Input which has a subform FRM_PlanChange_Input_Details.
On Load - I assign values to controls in the FRM_Planchange_Input and I am trying to assign values to control in the subform FRM_PlanChange_Input_Details.
On Load,
SetPropery -
Control Name: [Forms]![FRM_Planchange_Input]![FRM_PlanChange_Input_Details].[Form]![ASQ]
Property: Value
Value: =[Forms]![FRM_ASQ]![ASQ #]
I am copying the data in Form ASQ to Subform Plan Change Details. Whenever the macro is run, it gets stuck at the point giving an error "The control name is misspelled or refers to a control that doesn't exist. If the invalid control name is in macro, an Action Failed dialog box will display the macro name and the macro's arguments after you click OK."
Upon clicking OK, i get Error Number 3021 - Arguments [Forms]![FRM_Planchange_Input]![FRM_PlanChange_Input_Details].[Form]![ASQ]
The syntax I used is based on this article: Forms![main form name]![subform control name].Form![control name]. I also read that Subform Control Name and Subform Name are not the same?
View 3 Replies
View Related
Nov 12, 2005
Hi everyone,
I have a 4 part survey database consisting of one source table : tbl_survey. I have created a main form with 4 subforms (each subform is a section from the survey table tbl_survey).
I have placed 4 buttons in the main form, and each button when clicked reveals its subform, whilst hiding the others. So when the main form opens, the first subform is displayed (others are hidden). When the user finishes the first subform, they click on the second button to display the next subform and so on.
My question is, i want to set the verticle scrollbar position of each subform to the very top when its appropriate button is clicked so that if a user goes back to a previous subform, the scrollbar isnt all the way down the bottom.
Reptar
View 11 Replies
View Related
Mar 9, 2015
I am trying to get the properties of enabled and locked set in a continuious subform depending on a yes/no field in each record. I have tried using the answer in thread 160062, but this only works when it is not a subform (unless I have done something stupid!)If the main form is frmMain and the continuious subform is frmSub, where am I going wrong with this code which is in the subforms Class Objects?
Private Sub Form_Current() If Me.MaterialIssue = True Then CRMSignOffDate.Enabled = True
CRMSignOffBy.Enabled = True CRMSignOffDate.Locked = False
CRMSignOffBy.Locked = False Else CRMSignOffDate.Enabled = False
CRMSignOffBy.Enabled = False CRMSignOffDate.Locked = True
CRMSignOffBy.Locked = True End IfEnd Submany.
View 3 Replies
View Related
Jan 5, 2014
I have a form frmDisplayVars with a subform subfrmDisplayVars. The sub form initially has its RecordSource set to "" and is set dynamically from a maketable when needed.In the OnLoad for the main form, the subform RecordSource is set to a table "Table.Test". This is so that the load routine can execute a maketable query to create the form "Test" in advance. This produces a table from a crosstab query. All this works OK so far.
However, I wish to be able to double click on the datasheet in the subform to then open another form with data related to the cell/contol that was clicked on. Essentially creating a room occupancy table for a range of dates and rooms. Rows are rooms and columns are dates.
I have a problem with setting the OnBblClick for the subform controls.For example the 2 test code lines for the control "RoomName" illustrate.
Code:
Forms!frmDisplayVars!subfrmDisplayVars.Form!RoomName.Name
Forms!frmDisplayVars!subfrmDisplayVars.Form!RoomName.OnDblClick = "=TestMe()"
The first line works fine and returns as it should the name of the control "RoomName".It is the second line which I need to set the event code for OnDblClick which fails with error no. 2455 which suggests wrong syntax for referencing a control on a subform.I can't set the event procedures manually as they are not known in advance until the table "Test" is made so need to be able to set the control events dynamically.
View 8 Replies
View Related
May 5, 2014
I have a form that has five subforms on it. On Open all the subforms are unbound (so i can prevent the queries from running before the On Open event to speed up). Then i can enter my criteria for all five subforms in txtbox and click run. After I establish the source object for the subforms, i cannot establish the controlsource for several text boxes that pull the data from the subform, i get #Name?. her is a sample from the on click event... the top links the unbound subform and the bottom should then link a control in the subform to a control on the main form.
Me.Child167.SourceObject = "query.RP Sum Fuelman F1"
Me.Text71.ControlSource = "=[RP Sum Fuelman F1 Subform]![cntRecords]"
View 1 Replies
View Related
Aug 29, 2006
I have a form with a combobox to select a clients name and that pulls up that client in a subform and shows various records for the client. But it always opens up showing the first record and I want it to open up showing a blank record for keying in more data i.e. after the last record.
Can you help at all on this?
many thanks
View 1 Replies
View Related
May 4, 2005
Hi
I have a form and sub form, the sub form is populated from a query and has a column of checkboxes. Basically these checkboxes get checked as part of the form, but only a maximum of five are allowed to be checked. Is there a way of achieving this?
Cheers
Dan
View 1 Replies
View Related
Aug 8, 2005
I haven't been able to figure out the code to require entry (for a new record) in a field on a subform - anyone can help with this?
The situation is a contacts database, and each new contact should have a (or multiple) Person Roles entered as well, which is their relationship to the organization.
View 2 Replies
View Related
Mar 28, 2014
I have a Workorder Form that holds all the fields that apply to the entire project (payment, customer, etc).
Then I have a subform for Stock. This allows there to be several different stock items (and production information relevant to that stock) assigned to a Workorder.
I have successfully linked the two so that when a Workorder is opened, it shows any related Stock in the subform that has already been created.
The problem is new stock entries. I need it to only allow Stock additions to the current open related Workorder.
I currently have it where you can type in the WorkorderID (autonumber) on the Stock subform new entry and it will work. But I do not trust employees to type in the correct number every time they need to add stock to a Workorder. I can't release it this way.
I mean, the whole point is for the database to promote as little error as possible, right?
View 5 Replies
View Related
