Subform Datasheet Question

Aug 2, 2006

I have a subform which is basically a datasheet, I want to be able to update the items in the data sheet using vba, how would I go about doing this.

basically I have a combo in which the user selects a value I take that value and add it as a where clause in a select statement which returns a set of records, I want then take these records and populate the subform data sheet. I already have the select statement and know how to from a Filtered RowSource, i just not sure on what the call is to populate the subform. For a chart I've been using this

chtObj.Application.DataSheet.Cells(i + 1, j + 1).value = rsRowSourceFiltered.Fields(j).value

but a subform does not have the DataSheet.cells property

View Replies


ADVERTISEMENT

Datasheet Subform

Jul 11, 2006

I have a form and a datasheet subform in Access. A form has person info and datasheet subform has 4 fields: QuestionID, QuestionNumber, QuestionName and Answer fields. The Answer field is a dropdown field and has 3 items: Yes, No and N/A. If QuestionNumber from 3 to 6 the answer was Yes or No then lock the Answer field in Question 7. Any idea how to do it?

View 1 Replies View Related

Datasheet View In Subform

Jul 17, 2006

Hi. I am trying to create subform in datasheet view with one column (total 3)as drop down list. Is this doable? Thanks.

View 3 Replies View Related

How To Get Data From Subform As Datasheet

Jan 23, 2005

I have a form (Main) with a subform (MainSub) set as a datasheet.

The main form holds details of employees and the subform holds targets and dates for the employees.

the subform has 4 fields, Target, StartDate, EndDate, AchieveDate.

What i would like to do, is show the date of the target that was achieved last on the main form.

For example,

Joe Young has 3 aims, 2 of which are complete and 1 which is still ongoing.
Joe achieved his targets on 31/11/04 and 31/12/04.
On the main form I would like to show a control called 'LastTargetAchieved' as 31/12/04.

If I am not making myself clear I will see if I can get a sample uploaded, otherwise, thanks for any help.

View 2 Replies View Related

Subform Datasheet View

Jan 5, 2006

I have a subform that is in databsheet view. When the user logs in to the db it sets the global variable with the group level they have.

I would like a column to be hidden for a certain group of users and have tried the following.

On the subform opening event i have

if vlevel = "engineer" then

me.Hours.columnhidden = True

Else

me.Hours.columnhidden = False

End If

And i have done the same on the Main Form open event identifying the subform by Form!Subform![Hours].columnhidden = True

But neither worked.

Can any suggest something.

View 4 Replies View Related

Datasheet View In A Subform

Jul 25, 2006

Can anyone tell me how to manipulate a single cell in a datasheet view, or if it is even possible?

View 3 Replies View Related

Hiding Columns In A Subform Datasheet

Jun 3, 2005

Morning all, I'm trying to hide a column of a datasheet in a subform I have... if I run the form and right click and hide the column, it doesen't save that the next time I load it (wasn't sure if it was suppose to, but after reading http://office.microsoft.com/en-us/a...2362201033.aspx it would appear so) I'm using SQL Server 2000, not 7 so I would think that problem shouldn't come up for me yet it is, I need to hide a couple columns from the user (mostly ID columns) while they are entering in data, anyone have any advice they could throw my way?

Much appreciated

- Chris

View 5 Replies View Related

How To Create Unbound Subform (Datasheet)

Feb 9, 2006

Hi Friends,

I want to create unbound subform. As per your exprience can some one help me. subform must be Datasheet veiw. Thanks

View 3 Replies View Related

Before Update For Subform In Datasheet View

Jul 19, 2006

I have a small issue with the before update event, it is triggered for every record of my subform in datasheet view rather than just once when the user leaves the subform.

I have a module which is called from my form and all the subforms. It tells the user that changes have been made to the record and gives them the option to undo. This is to prevent accidental editing when viewing records. This works fine, but sometimes the user must enter a number of records in a subform and so they must answer the message box every time.

Is there any relatively straightforward way to accomplish the warning and undo when the user leaves the subform rather than the individual record in it?

A quick attempt at lost focus and before exit demonstrated that these were not the way to go.

For reference, here is the before update code and the module

Option Compare Database
Option Explicit

Private Sub Form_Before Update(Cancel As Integer)
On Error GoTo Err_Handler

Call Confirm_Change

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Here

End Sub


Option Compare Database
Option Explicit

Public Sub Confirm_Change()
On Error GoTo Err_Handler

Dim Msg, Style, Title, Response

Beep

'Define message box
Msg = "You have made changes to this record. " & Chr(13) _
& Chr(13) & "Click 'Yes' to save changes " & Chr(13) _
& Chr(13) & "Click 'No' to undo changes "
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Record changed - Confirm save" '

'Check which button pressed
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
MsgBox "Record has been updated with your changes", vbOKOnly, "Record saved"
Else
DoCmd.RunCommand acCmdUndo
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Here

End Sub

View 2 Replies View Related

Subform Datasheet Export To Excel

Mar 6, 2006

is ther any way to send the filtered subform's data to an Excel sheet?, I did som searching, but i can't locate anything specific
to what i am trying to do.

I have currently this code, but it is a huge dump of the database, and none of the form filters, my current MDB has the main form and it narrows down the search by clicking on the next forms links.
and on the last open form it will show a filtered result in a datasheet. this is what i am trying to export to Excel.

Code:Private Sub Command17_Click()Dim strqur As StringDim wurds As Stringwurds = "D:Documents and SettingsMy Documentsdownloadsmyfile.xls"strsql = "sortedby"DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strsql, wurds, TrueEnd Sub

is there any way to snd the filtered results to Excel? my brain hurst from scouring the forums.

View 3 Replies View Related

Modules & VBA :: Access - Get SubForm Datasheet Value Of Row?

Feb 27, 2015

I have a subform which is created with textbox and some command button in the top of the window, so this will simulate like a Datagrid.

[URL]

This is how it looks my datagrid when it fills from a Query.

[URL]

What I want is when I select a row from my subform, I want to Click the button "Cambiar", and get the value of the machine selected.

[URL]

View 4 Replies View Related

Forms :: Datasheet In Subform Won't Allow To Add A New Record

May 15, 2013

I have been handed an Access database (Access 2002-2003 file format) to clean up and update as necessary. Currently I am trying to add new rows to a datasheet that is in a subform. I have had no trouble in the past adding rows and for some reason now I can't.

In some instances I can update an existing row but never add a new one. Also, the records options are greyed out in the ribbon except for a select few that are not useful (refresh all, totals, more). Can't add new record from their either. I am in the main form that contins the subform which contains the datasheet, in form view.

View 5 Replies View Related

Forms :: Subform With Datasheet View

Oct 31, 2014

I have recently added a subform to a form and wish to display the subform in Datasheet view. Whilst I can get the proper data and headings to display, I cannot figure out how to get the column widths to persist. I can adjust the column widths while in (main) form view right on the subfirm columns themselves but after I save and reload the main & subform form, the widths revert to some sort of default value.

I have also tried adjusting the columns in the subform's associated data table itself as well as adjusting the text box widths when the subform is displayed in Form view but it does not affect the column widths when the subform displays Datasheet view.

What is more annoying is that I have another subform in which I was able to make the Datasheet view column widths persist but I don't seem to be able to properly recall how I did it.

View 2 Replies View Related

Forms :: Sum Function In Datasheet Subform

Oct 3, 2014

Sum Function in Unbound text on Datasheet subform..How to make a sum formula without count orange color rows Amount. Because that orange color rows is product return items so no need to count in SubtotalRs. Actually I have used below normal sum formula so it is ok but need to change the formula. how to give a formula without count orange Amounts?

View 1 Replies View Related

Calendar Displaying As Datasheet On Subform

Jun 8, 2012

I have a subform that users enter in Dates of the month and how many people attending specific functions. I dont want the users to have enter in all the Dates for each month I just want them to enter in the number of people attended. How can I do that on my subform called TrainingTanfTrainig. the Table is called Training_tbl thats a child table of the SpecialEvents_tbl.

View 1 Replies View Related

Select A Datasheet Row -- Populate A 2nd SubForm Based On That Row?

Jul 12, 2005

Hi,

I'm not even sure if this is possible to do (I havn't found anything on the Net yet), but I have a Form displaying Customer information, and the vending machines that customer has.

The main form (frmAccount) has 2 SubForms:
1) AddressSubForm (is a Datasheet), listing the many addresses the one account has.
2) VendingMachineSubForm (is a Continuous Form) and displays the machines related to the account.

What I want to do is select an address in the datasheet, and have the MachineSubForm populate with what machines are located at that address. Can this be done?

The Machines and Addresses link by AddressID. Both subforms are built on queries.

Thanks for any help/insight!

Mike

View 14 Replies View Related

Update Of Subform In Datasheet View On Mainform?

Oct 8, 2006

Hi,

I have a query (SelectedData_Query) , on which a form (SelectedDataQuery_SubForm) in datasheetview is based, which is placed on a form (NAWInvoerForm) where customer adress data is put in. When someone clicks new case button on the NAWInvoer_Form, another form (CaseDateTimeInfo_Form) is opened, data is entered, but when the "CaseDateTimeInfo_Form" form is closed, the data in the subform in datasheetview on the NAWInvoerForm is not yet refreshed. So I tried the following:

Private Sub Form_Close()
'------------------------------------------------------------
' RefreshNAWInvoerForm
'
'------------------------------------------------------------
On Error GoTo RefreshNAWInvoerForm_Err


DoCmd.OpenForm "KlantNAW_InvoerForm", acNormal, "", "", , acNormal
DoCmd.RunCommand acCmdRefresh

RefreshNAWInvoerForm_Exit:
Exit Sub

RefreshNAWInvoerForm_Err:
MsgBox Error$
Resume RefreshNAWInvoerForm_Exit

End Sub

Which works just fine. After the form is closed, the name, adress and cases overview form is reopened and refreshed, thus the case that has just been created is shown nicely in the subform which is based on a query.

But it is starting to become bothersome, sometimes when I close the form in which I create a new case, I don't want to have the other form (NAWInvoerForm) reopening again.

So how can i make it so that when I close the form only the data in the "NAWInvoer_Form" is refreshed, without having to reopen the form again. I mean, in the background, the form is still open anyway. It just shouldn't keep popping up everytime.

It can't be hard, but I can't see it. I'm new at this. I have found some similar topics on this forum, but none which helps me out enough.

It should be something like:

On "thisandthatevent" do a "thisandthatcommand" on "thisandthatsubform" in "thisandthatform"

If I know how the syntax of that is done, it would help me out with some other problems I see coming in the near future. I've looked at, and tried some things based on this link: http://www.mvps.org/access/forms/frm0031.htm
But without success.

Can anyone help?

View 4 Replies View Related

Using Subform In Datasheet View To Open Other Form

Oct 21, 2006

Well, I couldn't find this using the search, so here it goes:I have a form in which I enter data to create a new client. Within that form I have a subform in datasheetview which is based on a query which holds/retrieves certain (important) fields from a table that holds case info. So in that one view the client data is displayed on the form and all cases and the most important data from those cases is displayed nicely in one screen.Now, when a client calls I navigate to that client and see all previous and all current cases that are open and/or closed for this customer. So the customer says case this and that number is the case I need to discuss with you. Now, what I would like is to be able to click or double click anywhere on the line that, for instance, says case nr 55, so that the form(s) belonging to case nr 55 pop up and all info concerning that case is displayed and can be edited as needed. My problem is: I'm still a newbie and if it was a simple thing like using the on_click event to open the form that's needed, I would not have to post this question. But that can't be done (as far as I know) since the subform is based on a query which is displayed in dataview.Am I overlooking a very simple solution to this problem or is it really going to be as difficult as I think it's going to be?For clarification of the idea I have included a screenshot of the form in question. I assume there are tons of databases out there which use such a layout or idea as this one. Please, never mind the buttons etc. It is still a work in progress. ;)

View 12 Replies View Related

Subform - Datasheet Creating/updating Records

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

Forms :: Grand Total Of Subform Datasheet

May 6, 2014

After having no luck with the standard datasheet E-Totals, i'm now trying to have a textbox on the main form that totals a subforms column called lineTotal.Line Total is a calculated query field that works out the Qty*cost.So say if they put in 5 lines on the subform, the textbox field will sum all of there linetotal and shows a rolling grand total.

View 10 Replies View Related

Forms :: Obtain Record Value From Datasheet Subform

Dec 23, 2013

How can I obtain the value of a record from my subform in datasheet view?

Ideally I could double click in the "cell", for lack of a better word, and grab that value to pass through to another function.

On double click event? What then?

Edit: I suppose the user could select the row that the value is in as well. As long as I could then find the field I need from within the row. That might make things easier.

Edit 2: Sometimes I post before doing proper research. I've figured it out. Here is a quick example. The below code references an unbound subform called "Main_AU" where the field I want is "Description". The MsgBox is used to confirm that the code is grabbing what I want it to.

Code:
Dim test As String
test = Main_AU!Description
MsgBox test

View 3 Replies View Related

Forms :: Sum Calculated Field From Subform Datasheet

Feb 8, 2014

I'm trying to sum a couple of columns from a subform datasheet and carry them onto the main form.

My main form, [frm_tours_details], holds subform [sbfTours] which is a datasheet.

On [sbfTours], I've got a couple calculated fields within the datasheet, one, called "txtStudentSum", which calculates number of people total ([students]+[adults]+[numfreeteachers]) and one, called "txtptotal", which calculates fee for the program based on the following formula:

Code:

=IIf([txtMonth]>=10 Or [txtMonth]<=4,([Students]+[Adults])*7,([Students]+[Adults])*8)

(The fee is $8/person in season, and $7/person out of season.)

I've been following online directions I found to put a text box in the form footer which then sums each of these calculated fields ("txtStudentSum" & "txtptotal"), and then reference these text boxes from my main form.

However, the main form text boxes just come up as #Name? or #Error when I try to reference these boxes on the subform. Or, sometimes, the one calculating total students will show the total of ALL students, adults in the database, not just those for this particular record-but only, it seems, if I have the subform open separately first, then load the main form. I also tried putting the whole original calculations into text boxes on the main form, but they will only reflect either the current record selected in the subform, or they will only show the first record, regardless of what you've selected, depending on which way I tried.

I've attached a copy of the database ... click on the tab 'tours', then click on, say, tour #6 (since this one is multiple programs and needs to calculate) to open the tour details form.

View 1 Replies View Related

Forms :: Display Subform As Datasheet And Allow Filtering

Feb 9, 2015

I am using Access 2010. I have a subform(continuous form) that I display as a datasheet and allow filtering. I display the record count by using Me.Recordset.RecordCount in the subform's form_current event. This value updates properly based on the column filtering except when the filtering displays 0 records.

When there are 0 records, the form_current event does not fire and I cannot figure out how to trap when this occurs so that I can display '0 records found'. How I can tell when the column filtering causes 0 records to display in the datasheet? Is there an event that fires when the user filters using the column headings arrow?

View 5 Replies View Related

Forms :: Counting Occurrences Of A Value In Datasheet Subform

Feb 7, 2014

I have subform (datasheet) with a column for gender indicating "Male" or "Female" I wish to place the totals for each gender (and total overall) on the main form footer. I have tried objects of every type with calculated controls using conditional count formulas such as

=Count(IIf([Gender]="Male",0))

But I get #error on everything.

View 12 Replies View Related

Display Labels Instead Of Values In A Subform Datasheet

Sep 10, 2014

I have a subform that is displayed as a datasheet, and the source of this data is from another database. Essentially, it shows someone's name and status, but the status is displayed as a number. The people using the database don't know what this value means, so I was asked to display the label instead.

The values still need to remain in the database where the data resides, but can I use a lookup table or some other mechanism so that the label is displayed in the database the users are viewing? How exactly do I do this?

i.e...the data below resides in Database A but is displayed in Database B:
Col1 | Col2 | Col3
John | Smith | 2
Jane | Doe | 1

And in Database B only, it should show as:
Col1 | Col2 | Col3
John | Smith | Inactive
Jane | Doe | Active

Note that the users of Database B will not be updating Col3 data, they will just be viewing it.

View 1 Replies View Related

Multiselect Listbox On A Subform With Datasheet View

May 22, 2014

Is it possible to have a multiselect listbox on a subform with a datasheet view or do I need a combobox in this situation?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved