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.
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.
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.
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.
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...
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.
Hi, im having a problem with shring my db over a networkt,
as far as i understand access 97 locks a page (2k) of records when a record is being edited, does anyone know a way in 97 how i can get access to lock ONLY the EDITED record and not the page?
in Tools/Options/Advanced i have "edited record" locking clicked and each form locks only the record edited but its still locking the page, i know there is "record level locking" feature in Access 2002 but not in 97, is this possible?
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.
I have a main form that controls 2 subforms. sbfActiveProjects displays a list of records that have not been flagged as complete. Upon clicking on a record in sbfActiveProjects, the details of that record show up in sbfProject. This functionality works great, except when I try to create a new record. The code works fine for getting a new record, however once I run the command I lose my ability to navigate to other active records.
Code: Private Sub cmdNewProject_Click() 'set focus to sbfProject sbfProject.SetFocus 'navigate to new record DoCmd.GoToRecord , "", acNewRec End Sub
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
I have an "Returns" master form that contains two subforms. The subforms contain items that we are returning back into our inventory. The underlying queries in each subform show only those records where the "Return Date" is null. The query(s) works fine, except that if there are 3 items that need to be returned there are 3 records that show in the master Returns form. I tried the Totals option in the query but the I need that Return Date on the subform. I only want one Returns master form to show the 3 records (not 3 records of the same master form).
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.
I am creating a database that has a transport aspect to it.
Basically, I have a table with the list of buses, and tables for routes plied by each of the buses.
What I would like to do is that on a datasheet form, I would wish that if at the point of entering data, the data entry person selects Bus Number 1, then in the next field where the route goes, ONLY the options for the routes that this bus plies show up as what has to be selected from the look-up menu that appears. The same for the other buses and routes. Is this possible?
I have a scheduling database that I am only using one table "DateLog"
It contains these fields: JobIDInt-PK LotIDTxt-PK TaskIDInt-PK - List from TaskList Table TripIDInt-PK TaskDateDate ForemanIDInt - List from Foreman Table TaskQtyInt TaskMisc1Txt TaskMisc2Txt NotesMemo
Every time a trip is taken to a job site, a record is created. I would like to have a datasheet view of this table that will join all of the tasks for a particular JobID and LotID together on the same record for easy viewing. The list of tasks are static and will not change from job to job. The only fields that needs to be seen are the JobID and LotID at the row header. The rest of the data is the TaskDate for each task.
What I have attempted so far is to create a separate query for each TaskID and then to join them by the JobID and LotID. I have gotten to the point that I can put values into the datasheet, but I get an error that the primary key cannot be null.
Am I approaching this the correct way? Is there a way I can point the hidden key fields to the fields they are joined to? Access does not seem to be smart enough to figure this out itsself and I am not sure how to tell it to create the new record using the fields that it is related to.
This is very difficult to explain, any help would be greatly appreciated. I will be glad to post further clarification if I did a bad job explaining what I am trying to do.
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:
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?
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.
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
In the code below I am creating a record with the INSET INTO statement and then Updating with additional info in various fields. it is not working the way I thought it would, so I am trying to create the record in it's entirety.
Code: SQL_Grade_GUSD_ID = "INSERT INTO Grades (GUSD_Student_ID) VALUES (" & Me.GUSD_Student_ID & ")" SQLM1_1_ELA = "UPDATE Grades SET Grades.Subject = ""BM1(ELA)""" SQLM1_2_ELA = "UPDATE Grades SET Grades.Type = ""Exam""" SQLM1_3_ELA = "UPDATE Grades SET Grades.Score = ""0""" SQLM1_4_ELA = "UPDATE Grades SET Grades.Nam = ""GUSD BM-1""" DoCmd.RunSQL SQL
[Code] ...
I am running to syntax problems when I try to USE the INSERT INTO to create the record with all the info in one statement.
Code: SQLM1_1_ELA = "INSERT INTO Grades ( GUSD_Student_ID, Subject, Type, Score, Nam ) " & _ "SELECT (" & Me.GUSD_Student_ID & ")"" AS GUSD_Student_ID, ""BM2(ELA)"" AS Subject, " & _ """Exam"" AS Type, ""0"" AS Score, ""GUSD BM-1"" AS Nam " & _ "FROM Grades"
I am Getting this error:
Syntax error (missing operator) in query expression '(12345)" AS GUSD_STUDENT_ID, "BM2(ELA)" As Subject, "Exam" AS Type, "0" As Score, "GUSD BM-1" AS Nam From Grades'
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?
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
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
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.
