Modules & VBA :: Data Entry - Subform Creating Orphan Records
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
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
I need to alert the user of the database in case he/she enters a record that already exists in the database. If a person enters a key type and a serial number combination that already exists in the system and has status "issued", I need a pop up message to show up.
Am I missing some quotation marks somewhere in that DCount?
Private Sub SerialNumber_AfterUpdate() If DCount("*", "tblIssuedKeys", "KeyType = '" & Me.KeyType & "' And "Status = 'Issued'" And SerialNumber = '" & Me.SerialNumber & "'") > 0 Then MsgBox "This key has already been issued" Cancel = True End If End Sub
We have a client who is using an Access 2013 DB (Office 2013 Pro Plus) with the Windows 10 operating system. They are working on implementing the infrastructure for using Office 2013 under the Office 365 subscription. The Access DB they are using is setup as 2 .accdb files, one that contains the back end data, and the other the objects for the UI.
They will soon be using MS Surface Pro tablets for their field personnel, and want a form that can be used for data entry while in the field.
We talked about creating a form that could be used offline (basically a new FE and BE) and routine that would do data synchronization back to the network database when submitted by the field personnel.
From online research, it looks like this may be better solved with the use of Office 365, SharePoint, and Access Services. Unfortunately, since they haven't yet migrated to Office 365, we have no way to test it and need to determine if it is a viable option, or if we will need to do the "custom routine" option. Since they are planning to migrate to 365 anyway, if it works, it would obviously be a more cost effective solution for them.
Here are a few questions we have when considering this solution:
1) The Internet is not always available in the field, so the solution must be one that can run offline. Is this possible with 365? 2) They are working to get Sharepoint Services working soon. It looks like we will also need them to implement Access Services. What do SPS and Access Services look like, how do they interact, and what features do they offer and how can we leverage them? 3) What does the web-based Access application look like? What is the path to convert/migrate an Access 2013 DB to a web app? Does all development need to be done (or redone) in a new dev environment for 2013 web apps? 4) The "hydration" of an offline copy of the form is what we would need to distribute to an offline tablet device. What does this involve? Is a small footprint .accdw created? What happens after that when first accessed by the user? Is there a hydrated .accdb for the Front End and/or Back End? 5) If we must hydrate the entire application, we may need to implement security at that time. From our research, it appears that once the Access web app is published to SPS, the objects (lists, etc.) in SPS are lockable at the SPS level, and we may have to use that security layer instead of Access security, correct?
I am tasked with creating a scientific database of fish catches at various different sampling stations. I envisage the entry form having a hierarchy, ie year, season, date, sample station, species caught, and then the specific associated data with each species (weight, abundance, length etc) will be a distinct record.
As fas as data entry is concerned, I would like the user to not have to re-enter the same information over and over when entering multiple species entries at the same sampling station, on the same date and so on up the hierarchy.
How do I create a form that will allow me to enter data into three different tables?
I have a rather simple database for tracking students. When I get a new student I need to take information off their paper application and enter it into three separate tables, Student, Families, and Demographics. Each of these tables contains the fields StudentID, StudentFirst, and StudentLast.There is a relationship between the StudentID field on each table, with Student being the main table and Families and Demographics coming off of it. All relationships are one to many. StudentID is the key for the table Student.
I want to create a form to add a new record to each of these tables. I want my data to be displayed in the Columnar style. I only want to enter StudentID, StudentFirst and Studentlast once and have it populate to all three tables.
I can create a form and a query that displays existing entries exactly like how I want to enter them, but of course I can not edit or add to them. I have tried using subforms, but they don't seem to link up. Once I enter the Name, I want it to be on all the forms. I don't want to have type it three times. I also tried creating three separate forms and connect them with the Navigation Form. The issue again is that once I enter the name in one form, it is not automatically on the next form.
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
I am creating a form for data entry into my main table. It shows all the fields including a name, date, id number etc.
I am trying to put a subform on the form containing a query. The query has two filter parameters fed from two unbound boxes on the main form (date and name). Their purpose is to filter records on the query to just show those for one person on one day. It also calculates a duration based on a start and end time. The query results are ok.
I can't get the query to display properly in the subform.
Subform Properties/data/source object is set to the query name. I'm told i have to link the master fields and child fields but when I try I get the error 'can't build a link between unbound forms'. I thought all that was necessary to bind forms was for there to be a common field between the form and query? Both contain fields from the main table including date, name, start time and end time etc.
I have a form that lists records in a table. I would like to have a hyperlink beside each record that will open the data entry form and navigate to that specific record. Right now, the use can only open the form for all records and has to use the record navigation buttons to find the desired record.
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.
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 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.
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?
First I would like to give thanks to all the knowledgeable folks here who have helped me with my DB to date. It is working and every one is very happy and I have learned a lot.
So now I would like to add some more functionality to this existing project.
My DB is for data input of customers for a drawing. It has the following fields: Id, account number, first name, last name, date/time, score1, score2.
I t is taking a great deal of time for the users to enter in hundreds of entries a day. Most of the entries are customers who are already in the DB. I would like to get the fields to auto fill the data for existing customers say after the account number is entered. So after you put in the account the name and any other pertinent data would shows up saving users from typing it in again.
The first problem I am having is that this is still a data entry form and I can’t figure out how to be able to see the account information and still add new data to the record? The new data is a daily score they get.
Second I haven’t figured out how to call up the customers information from just the account field.
I’ve googled this and haven’t found anything terribly helpful.
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 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....
I want to use the same form in datasheet mode for data entry and retrieval. When retrieving, all controls are disabled and locked. I am trying to enable and unlock them for modifying but that isn't working.
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?
I have a form with multiple textboxes and comboboxes that allow for user entry, and a subform displaying a table, where the entries from the form are saved and can be retrieved. It's basically a way of creating new task entries and editing existing ones from a single interface.
One of the fields is an "issue number" of sorts, which is a 5-digit code that identifies the task. We have a website where details of each task are stored, and the URL format is akin to this: [URL] ....
The functionality that I'm hoping for is that when the user saves the record, it will be saved as the full link address, by concatenating the static first portion of the address and the code entered by the user. However, I need the table to still only display the code, not the whole link address, and will follow the full address when clicked in the subform table.
I am creating a simple data entry form wherein the user will enter the product id and on change the product description and retail price must be displayed.
When the order quantity is entered, the total cost need to be calculated. I am able to do this using DLookUp and simple multiplication.
However, after doing the above, I need another row to appear so that I can accomplish the same for another product.
How to give a VBA code for duplicate records is without entry in Subform datasheet. I just used in the screenshot code but that is not working properly. how to change a vba code for without entry duplicate names.
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 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.
I have some questions about a schema design I am working on. After much trial, it appears to me that a particular table is not and probably does not need to be related to any other table in the schema. So my questions are:
1. Is it right or quite typical for schemas to have some tables which are orphan (i.e. unattached to any other tables)?
2. If tables can exist in isolation, then what are the primary purpose they provide? I have a suspicion that if fields from an orphan table are used in query etc, then the possibility of Cartesian joins will arise. If so, how does one take care of avoiding such a problem?
