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.
I have a form puling from one table and then a subform in that form pulling from another table.I have a field in the [FORM] called [GROSS REFUND] and a field in the [SUBFORM] also called [GROSS REFUND].I want the value of [FORM].[GROSS REFUND], if the value is not zero, to be copied into a new record on the [SUBFORM.[GROSS REFUND].
I have a main form that has some sub forms on it linked by master child on PK. When I select a new record on the sub forms I want to take a date value from the main form and populate the value into a field in the sub form. How do I do this as I cannot see a new record event on the sub form?
I believe I did this before awhile ago but for some reason I keep getting an error.
I have a Mainform (frmMain) that has a Subform (frmSub). On frmSub I have 2 comboxes (strCom1 & strCom2) one is set to invisible (strCom2.Visible = No).
So using the "On Open Event" of frmMain I want make strCom2 visible if strCom1 = "Read Only". Ofcourse I will also need to place the vba on the On After Update event. Below is what I have so far but doesnt work.
I get Run-time Error 2427 "You enetered an experssion that has no value"
Code:
Private Sub Form_Open(Cancel As Integer) If Me!frmSub.Form!strCom1.Value = "Read Only" Then Me!frmSub.Form!strCom2.Visible = True Else Me!frmSub.Form!strCom2.Visible = False End If End Sub
I have a continuous subform which queries a table of attachments. I'm using Access 2007 but not using the Attachment datatype; this DB will grow considerably and I don't want to waste precious space by filling it with bulky files. So instead, I have code which makes a copy of the attachment and adds the hyperlink (to the copy) to the table instead.
Now - I want to add a control to the subform to display an icon / image reflecting the file type of the attachment (Word doc, Excel s/s, PDF etc.)
But not sure how to go about it.
I was thinking I could use FileSystemObject.GetFileExtension at the point in the code where the attachment is added, and add a new field to my attachments table (i.e. translate "*.xls*" to "Excel", etc.) Then store / embed a handful of images for the core types I would expect and use an image control on the subform to display the image based on the value of that field.
But is that even possible / feasible?
Or, is there a handy API which can retrieve the icon associated with a file type based on what has been installed on the local machine (even if there is, there's still the problem of setting up the image control to display the appropriate icon specific to each record...)
Or, is there another control available which would be better suited to something like this than an Image control?
I'm trying to make a report where i will have a variable number of charts. My approach is to have one "template" chart, and copy and paste it at runtime, and then alter each new chart's properties (data source, etc) to suit
My question, and i'm sorry if it's basic, is how do i copy and paste my first chart (called "Chart1") as another control named "Chart2"?
I need to duplicate a number of controls on a form. They are all acCommandButton types, but they have different images, sizes etc.
Anyway, my thought process was if I have a "template" acCommandButton then when I create my new controls if I could use this template then the code I have hanging off them (I have a function being called on the onClick event, borders are set to hairline, themes turned off and various other settings) would be copied. Save me trying to set them all individually.
So, my question is, can I "copy" a control in form design through VBA. Once I create the copy of the control I can then change the couple of properties I need to and "hey presto" I have skipped a load of settings that I don't need to set.
At the end of the process when I rename my form I can delete my template control and I have a working form.
I am still trying to copy an Excel file from a parent folder to a sub-folder, that this code creates, and name the copied file with the name used to name the sub-folder, using the name entered in a control field (named 'TheDirName) of an Access 2007 form. Here is my VBA, which names the copied file "TheDirName.xlsx.:
Private Sub MakeDir_Click() Dim appExcel As Excel.Application Dim lngLastDataRow As Long Dim Folder_Path As String 'Create path for customer folder & files
OK. I have searched and searched and every thread dances around similar situations but none seem to address this particular one. I am trying to make a control on a sub form visible/not visible depending on the condition of a control on a main form. The catch is that the control I'm trying to change the state of is on a subform located in a tab control. I've tried a thousand combinations and none seem to work. How do I reference the control on the subform in a tab control?
Main Form: frmMemberMain Tab Control: TabCtl12 Tab Control Page: 2 Subform: frmChildren Control on subform: txtRelationship
Can someone please help before my brain explodes!! Thanks! :eek:
I have table that I had to add a new field to which we update with a form. I tried to add a control for the new field but the field does not show up in the list for the control source. I am trying to add a list box to the form with a blank and 5 options.
I have attached screen shots of the table design and the form. The table has the field in datasheet view and I have manually entered a few entries in it but it still will not show in the control source for the form control. The top section of the form is where we enter and select the data for the new records. The bottom section (circled in red) autofills the matching record, from separate tables, for updating with the new entries.
I have added form controls for modified fields in the past so I am confused about why this is happening.
The Format Painter seems not to work in Access 2010.The Format Painter button is not in Home / Text Formatting, where you might expect to find it.I create a New Group and add the Format Painter icon. I cannot then use it. It stays greyed out.I add Format Painter to the Quick Access menu. It stays greyed out.If I view a form in Layout mode, I can sometimes get the Format Painter icon to highlight - but I cannot select just a single cell whose format I wish to copy.
Where use of the Format Painter is documented..How to do a very simple thing: copy a format from one cell in a subform to another.
I have a subform for which I want to loop through all the records and then copy one of the fields for each row into another sub form.
I came up with the bellow code but i get an error when I run it.
The error is an "error 438".
Code is at follows and I am copying the field called price:
Do Until Forms![Roll Out - Site Form]![Roll Out - Sign items pick list].EOF [Roll Out - Sign items added].Form![Price] = [Roll Out - Sign items pick list].Form![Price] Forms![Roll Out - Site Form]![Roll Out - Sign items pick list].MoveNext Loop
Okay. I am certain that I am missing something real easy. I have a somewhat large, form. I needed to offer users a similar form, but with a few field changes.
So, I copied the form, added the new fields. If I work with an existing record and do not change the 1st field's data (idnumber), then no problem. BUT if I try changing that idnumber or adding a new record, I cannot exit that 1st number. There is no afterexit info for it or other issue that I can see. (But am I 'blind'?)
My table called Table1 has a primary key field (called RefID) with AutoNumber format. My problem is I want duplicate value of RefID field to another field called DupID. I want DupID to be changed, but I don't want any change on RefID field whenever I make changes.
I had a form with two tabs and then added a subform into a third tab. Until I added the subform I could see the tabs at the top in both the design view and the form view. However, since adding the subform, althougth the form looks OK in the design view, when I switch to the form view the tabs are missing. The only way that I am able to get to them is to tab through all my fields until it eventually seems to shift the page up and I can see them. If I switch to another tab and this stage I then have to go throught the same proccess of going through each of the fields until I can see the tabs again.
How do you tab out of one subform and into another from the main form. I have 3 subforms and after a user is finished entering data into the main form the first subform sets focus. After data is entered into the subform it’s impossible to hit the “enter” or “tab” key to move onto the next subform.
I have two forms, ctrlpanel and users. ctrlpanel is acting as the main form and users as the subform. In a module, I have the following code inside a function that is called on in the users subform:
Code: With Forms![ctrlpanel]![users].Form!cmbSection .RowSource = strList .RowSourceType = "Value List" End With
Can't seem to figure out why I get this error. Have tried a couple different ways of doing this with the same result. here is the error: "Microsoft Access cannot find the field 'users' referred to in your expression."
users isn't a field, it's the subform and the syntax above is how many sites show how to refer to the subform.
I have a form called frm002_PAF_MonitoringMAIN and on this form I have a subform called frm002_PAF_Monitoring. The subform has a button to another form for contact details called Contact_Details. The Contact_Details form opens with the details of the person who I have selected on frm002_PAF_Monitoring. There are some fields on frm002_PAF_Monitoring that I would like to be populated on Contact_Details when opened, like FirstName and LastName. How can I do it?I have tried on afterupdate event on Contact_Details below but those don't work:
I have a subform on my main order form. I would like to click a button on my main order form and have it change a control on the subform. For instance, I have 2 controls on my subform, control 1 is a text field, and control 2 is a yes/no box.
When i click the button on my main for order form, I would like it to evaluate control 1 on my subform so that it can adjust control 2.
I would like to see if control 1 is null then set control 2 to yes or true. Thank you in advance for all your help.
how can i focus on a subform control? my main form called form A has a subform called form B. Form B has a button that, when is clicked, starts a new record for Form B source. all works ok while editing form B but when i open Form A and run the same macro, i get an erro saying Form B is not open.
my macro reads- GoToRecord object Type Form Object Name Form B Record New
the button is placed on form B which is sub form to Form A.
This is probably simple, but I can't figure it out.
I have a table called Household that has a one-to-many relationship with a table called Individuals. The Individuals table has fields called FirstName and LastName. I have a main form based on Household. I want to have subforms in a Tab control based on the Individuals table. So when you click Page 1, it shows the data for the first person in the household, and when you click Page 2, it shows the data for the second person in the household. This would work by basing the subform on Page 1 on a query that finds the Min of FirstName, and basing the subform on Page 2 on a query that finds that Max of FirstName. However, the data in the subforms would not be updateable, because queries based on aggregate functions are not updateable.
Does anyone have any ideas as to how I can accomplish what I'd like to do?
I've use a set up that works fine on a form but doesn't when used in a subform. Basically, i have a check box and txt box that won't allow anything to be written in it unless the check box is checked. If it is checked and nothing is inputted in the text box, the lostfocus event for the txt box flags the user. It works fine in a normal form and when it's a subform, the lostfocus event does fire when going to another control in the subform. The problem is that it doesn't fire when going to another control not in the subform. Is there anyway I can get the lost focus event to fire under these circumstances?
