Forms :: Sales Invoice - Updating Many Records In Datasheet Subform
Jul 4, 2013
I am trying to build a sales invoice for a pharmacy that contains a sub form in a datasheet view. The main form contains only the invoice number and date while the sub form contains the drug code, drug name, Qty., Available Qty. and Updated Qty.
Presently, I can only code the update to takes place on each line of the datasheet. I want the update to take place at the end when I must have finished inputting all drugs bought with same invoice number.
View Replies
ADVERTISEMENT
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 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
Jun 23, 2015
I have a navigation tab with 6 sections, of the 6, 5 of them are self-made split view with a form view on top and datasheet on bottom and upon adding records through a command button on form view I would like the datasheets to update.
On other forms it would (I believe) update to the bottom of the datasheet, now on the ones I've been banging my head against it either replaces the top record or doesn't show up unless I switch tabs and switch back. From my searches I assume this is in need of a requery...
View 14 Replies
View Related
Jul 30, 2013
I have a main form and subform. The main form is bound to a table of invoices and the subform to a table of invoice items. I'm picking up the invoice number from the form to save it to the invoice items table, so I need to save the record for the invoice before saving the invoice items from the subform.
View 4 Replies
View Related
Apr 1, 2015
I am having an issue trying to show all records when I load my form. It loads correctly when I don't have a record source for the main form. However, the combobox filters will not work. My goal is to have users be able to use the datasheet, subdatasheet and combobox filters. I can get the form to work just fine when I link the Main form and subform, but when the form opens it is filtered on the first record. I have been successful with this approach when using other forms, but they didn't require a subdatasheet.
Is there anyway I can have the comboboxes to filter yet be able to show all records until the user selects filters? Is this possible?I finally got my Manager to agree to use a database instead of Excel files based on this form setup and I need to make as "user friendly" as possible (look and function like a spreadsheet) I attached some pics of the form along with the code for the filter.
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
Dec 21, 2006
Hi, I have a problem I don't know how to solve it. I have a subform in datasheet view, linked to the main form. There is a running total on the main form based on a dsum on a field of the subform.
When I delete a row on the subform datasheet, the main form calculation is not automatically updated. I had to create a "update" button to do the refresh.
Is there a "on record delete" event available for a datasheet sub form?
Thank you for any help.
View 1 Replies
View Related
Apr 7, 2014
I have a Navigation form which contains a subform and on that subform is a datasheet.
Navigation Form -> Navigation Subform -> MyForm -> MyDatasheet
I have been having a hard time getting the datasheet to update - I've tried several methods (refresh, requery, repaint, etc). Even pressing the "Refresh All" button on access takes 1 or 2 refreshes before my data is updated.
View 4 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
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
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
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
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
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
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
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
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
Oct 10, 2014
Is there anyway that we can do cascading combobox in the same datasheet view of subform? Basically I'd like to filter "Account Name" based on "Plan#" selected in the same record of subform.
View 3 Replies
View Related
Nov 22, 2014
I have a subform in which I display a table in Datasheet View. The problem is, I cannot figure out how to place the columns in the order (i.e. from left to right) I want.
For some reason, right-clicking on a column header in Form View and selecting Freeze Fields will shift that column to the left but I can find no consistent pattern to it and no other way of shifting a column. Reordering the columns in the table itself seems to have no effect on the order they appear on the subform and moving the controls around in the subform in Design View also seems to have no effect.
View 9 Replies
View Related
May 19, 2014
I have a subform that is essentially a blank table which users can create rows depending on how many they require and fill the table in.
I then want to be able to append a main table with the records that have just been created in the subform's table... but here is where an issue lies.
Main Table has 6 columns... subform table has 5.
I already have the value to put into the 6th column but Im just not sure how to do it... the value is obtained from the original form.
For each record, copy that record into the main table + adding another value into the extra column.
Is this possible? or doable?
View 4 Replies
View Related
Dec 9, 2013
I am trying to apply a filter to a datasheet subform using a combo box.
This is the code I have used on the "after update" event:
[Ordering - Price list items].Form.Filter = "[Secondary Category]=" & Combo72.Column(1)
[Ordering - Price list items].Form.FilterOn = True
When I run the query i get the following error:
Runtime error 3021:
No current record
I am not sure why it isn't working as this is how I have set up other combo box filters and it worked fine.
View 6 Replies
View Related
Aug 10, 2014
With a particular client, they want to display a subform in datasheet mode. They do not want an alternative like continuous forms. If I am displaying the subform in datasheet mode, is there any way to eliminate the header row which contains the names of the columns in the subform? I know how to remove the captions themselves but the header row is still there. I don't think it can be done but I wanted to check here with those more familiar with this.
View 6 Replies
View Related
Aug 8, 2014
I have a table of events, tblActivitiesDates which have an activity type and a date.I have a form which has a datasheet with a query source (a separate datasheet form embedded in this one), and 2 radio buttons. The query that is used uses radio buttons this way:
-Activity Type Filter: A radio button, that when clicked a combo box appears, with a list of activity types to choose from. Basically, filtering by Activity Type.
-Date Type Filter: A radio button, that when clicked 2 text boxes appear, in which you write the dates between which you want to see the events organized in the table. Basically filtering by dates.
To make reading easier, Activity Type Filter=ATF, Data Type Filter=DTF.There are 4 possible options:
-ATF=ON and DTF=ON
-ATF=ON and DTF=OFF
-ATF=OFF and DTF=ON
-ATF=OFF and DTF=OFF.
For each option, I have written a line of code in the appropriate OnChange/AfterUpdate/Whatnot events in the form: CurrentDb. QueryDef ("qry ActivitiesDates"). SQL = "SELECT..."according to whatever option is currently active.
When I enter values into the form, and instead of embedding my datasheet form I simply open it every time from the navigation pane in a new window, the datasheet is filled appropriately and works 100%. Of course, I have to reopen it every time I change something so the query will rerun.After the table crashes (will be explained soon), I re-embed it to the form before trying it again. The first time I re-embed, no matter what I do, the query doesn't update nor crash.
Say I start with the option ATF=ON, DTF=OFF, it will work fine, updating when needed, but if I tick DTF=ON, regardless of the dates I enter, the table will only change according to the first option (While when open in a new window will show the correct table entries), ignoring the fact that the QueryDef. Sql has changed. Once I tick ATF=OFF, the table crashes and I get the error:"Run-time error'3420': Object invalid or no longer set".The exact same thing happens when I go DTF=ON,ATF=OFF. It ignores the event types I enter, and once I tick DTF=OFF, it crashes with the exact same error.
I added Me.Activities_DS.Form.Requery (datasheet's name is Activities_DS) in every place it should update.
View 8 Replies
View Related
Dec 2, 2014
In Access 2010, if you have a form with a subform in datasheet view, and you move the focus away from the subform, how can you tell which record(s) on the subform are selected?
When the subform has the focus, the selected row is shown with a blue/grey tint. But as soon as the subform loses the focus, this disappears and all rows look identical.
Its an issue as I have 2 datasheet subforms on my main form, with the 2nd showing records related to the record selected in the 1st. But as soon as the 2nd subform receives the focus, it is impossible to see which record in subform 1 was selected.
I have tried conditional formatting, but that does not work.
View 1 Replies
View Related
Aug 15, 2013
I have a subform whose design I am trying to change. When I make changes such as moving fields or adding fields the changes appear in form view (and layout view), but not in datasheet view. However, if I delete fields, the changes appear in both form and datasheet view.
This situation results in behavior where I can have the subform open in design view and toggle back and forth between design and datasheet view, and they are different. However, toggling between design and form view shows the changes as having taken effect.
View 7 Replies
View Related
