Check Data On Leaving A RECORD- Record, Subforms And All
May 24, 2005
I have an application that enters candidates in, who apply for a job.
I found that I can enter a candidate in, and if I dont enter at least an address type or phone type ( giving that candidate an address record, albiet a blank one (just a type of address) and a phone record) then the candidate wont have a sub record for addres or phone at all.
This is hurting my reports. They are set to pull the primary address and phone only, as we dont need to have 2-3 contact information for a candidate on a report This is how I found out that I had blank address and phone for some test records. I need to be able to check when a person does a number of things, that at least the combos for phone type and address type have been chosen as something. This will ensure that they have a primary address and phone, as I have code that takes care of all that. I tried putting the following code in the form's before update, but since it is calling to a subform, the minute I tab TO the address combo, I get the message. The form is updating before I get the chance to skip the field.
Here is the lay out. A user can enter in candidate, their address, phone, application and activities. Address, phone, applications and activities are all sub reports on a tab control. The user should be able to move freely within any record, and only be bothered when they try to leave, if important information is still empty. I dont want to control how they enter ('you must enter this first!' lol) just that they do enter it in the end.
What I want to do is check when the user is leaving a record.(closing form, going to another record, clicking the search button that opens another form etc) if either combo is empty (no primary contact info) then it gives the message and goes back to the combo that is empty.
Here is the code I have. If anyone can help me find the right place to put it, I would really appreciate it. Thank you,
If IsNull(Me.[subAddressEntry].Form![cmbAddressTypeID]) Then
msgbox "You must enter an Address"
Me.[subAddressEntry].Form![cmbAddressTypeID].SetFocus
Cancel = True
ElseIf IsNull(Me.[subPhoneEntry].Form![cmbPhoneTypeID]) Then
msgbox "You must enter a Phone"
Me.[subPhoneEntry].Form![cmbPhoneTypeID].SetFocus
Cancel = True
Else: Cancel = False
End If
If I can't figure this out, I am thinking of making a new address type and phone type. Call it "none chosen" and then make that a default selection. Then one would always be chosen...I think. I would like to avoid that though.
This is, I think a very easy question: What would be the apropriate form event to use when leaving a record on a single form (When you press next record for example)
I have two subforms on a tabbed page. Both subforms read/write data from the same table. The first subform is in dataset view and consists of 5-6 fields on the line containing class information. The second subform consists of two memo-type fields where users can enter comments. The fields from both subforms should be written to the same record in the table. The problem is that the information from the second subform is being written to a different table record; it is creating a new autonumber. The data is being written to two separate records instead of being combined into one.
How do I link the two subforms so that the data gets written to the same record in the table so that the class information and it's related comments stay together?
We have a table, JobRegister that people enter information on using a form, FrmOrderEntry
I would like to run a check when a serial number is entered in the field SerialNo to see if the same serial number exists where CompletionDate is null.
this would stop duplicate open orders being put on the system, or new orders being entered where the previous order hasn't been completed.
I'm pretty new to Access, so please overlook any stupid questions :) I have a data entry form that has one field that cannot be a duplicate. So, in the table I have it Indexed with No duplicates. The problem is that the form doesn't notify the user that they have entered duplicate information until they close the form. So the 10 minutes they spent filling it out is wasted. So, after consulting various posts, I did a BeforeUpdate event with the following code: (the field that must be unique is called "LS Number", in the form "Q and D" based on the table "Q and D Database")
Private Sub LS_Number_BeforeUpdate(Cancel As Integer) On Error GoTo LS_Number_BeforeUpdate_Err
If (DLookup("[LS Number]", "Q and D Database", "[LS Number]=Forms![Q and D]![LS Number]")) Then MsgBox "The LS Number you entered already exists. Enter a unique LS Number", vbInformation, "Duplicate LS Number"
When I test this by entering a duplicate LS Number, it gives me the error "Type Mismatch." Unfortunately, I have no idea what this means, nor do I know how to fix it.
Have a strange problem. I've created a form with multiple subforms and if there is a record for the subform to display everything looks fine. However, if there is no record for the subform it does not show anything on the screen or if printed.
I would like for the fields on the subforms to show no matter what but I can't figure out why it doing this. I've checked the display parameters and everything is set right.
How do I make the forms show no matter if there is data to display?
What would be the best control source to use for my sub forms detailed below:
I am designing an Asset Management System. Each the main Asset Table "TblAssetMain" has child tables "TblAssetType", "TblAssetDetails", "TblAssetEventLog".
When a new asset is created, the AssetMain form will have subforms for these three child tables (linked by Asset_ID = Subform![Asset_ID]).
Can I use a query of the child tables for data entry or, if I set the control source to the actual tables, can I limit the number of records in the child tables for each record in the Asset Main table to "1".
Basically each Asset will have one record in AssetMain and 1 relating record in each of the child tables i.e. you cant have more than one AssetDetail record for one Asset.
Did do a search and found the following code but not sure whether there is a way without using code?
Code: Private sub Form_BeforeInsert(Cancel as Integer) If Me.txtCount > 2 Then Cancel = True Me.Undo Msgbox "Please return a book before checking out a new one", vbOKOnly End If End Sub
Purchase Order (Supplier, Order Date, Tel etc) Purchase Order Details (Item Description, Quantity, Price etc)
This way I can have several items in 1 purchase order.
I now need to create a 'New Purchase Order' button. I've tried doing this but it only creates a new record for the form where the button is placed i.e. if the button is in the 'Purchase Order' form a new record is created for Purchase Order but NOT purchase order details.
Is there a way I can get the button to add a new record to 2 forms at the same time?
I have a form with some tabbed subforms. Master/Child links are set, everything is bound. When I open the form to display records (based on search criteria on the switchboard form), let's say it returns 6 records for someone, they are sorted by date and so the newest would display first.
But when it opens, the main form shows all the appropriate data, but the subforms don't show at all. Just a blank box where the subform control should be. If I click next record (and any thereafter) they all display correctly immediately. Now if I go to the 2nd record, then back to my 1st record, it displays correctly.
I'm guessing it must have something to do with the onLoad event of either the main form or subforms, but I don't have anything in there for OnLoad....no code at all.
Right slightly complicated (in my eyes) and I have not found anything in previous threads to help but here goes:
I have a main form (FRM_SUMMARYREVIEW) that has an unbound subform field on it (subfrmmaster).
When FRM_SUMMARYREVIEW is first opened subfrmmaster has its sourceobject set to a form called FRM_OPENSUMMARY.
This subform lists all my open escalations, in a continuous forms format and has a field in it called "escalationid" and when I click this field "subfrmmaster" changes it's sourceobject to another form called FRM_MAINDATA and should display the data relevant to the escalationid I have just selected.
In the past I used to click on "escalationid" and it opened up FRM_MAINDATA as a new form, using the following code:
However how can I get the subfrmmaster sourceobject to change and display the relevant escalation detail ?
My other option is to have two subforms on the main form, one with "FRM_OPENSUMMARY" the other with "FRM_MAINDATA", as "FRM_OPENSUMMARY" has a list of escalations displayed, in a continuous forms format could I set it so as I arrow down the list, the data in FRM_MAINDATA displays the highlighted escalation in "FRM_OPENSUMMARY".
How to fill values in a new record with data from previous record?
I've total 11 columns in a table and values in 3 4 columns are repeating for few rows before it needs to be changed eventually. I want to fill these rows with values from previous record.
I am working on a database and i have notice after making a form that when i enter a record using a form if i enter incomplete data on the form access automatically save that record and generate a id for that record.
For example I have a table that contain
StudentID,StdName,FatherName,DOB,Adress,Phone
And I have created a form for that table that also containing these fields.
Here I want to do that on the form I want a "Save" button , and the purpose of this button that when I click on this button then MS Access Save the record and then generate the ID for that record and if i close my form without pressing "SAVE" button access do not save that incomplete record.
The fields we want to focus on is "AccNo" and "TestCode"
Now i want to filter based on this condition:
If "AccNo" AND "TestCode" BOTH have duplicate data in a record. Show that record only once. Remember. . . "AccNo and TestCode, both these fields have to have duplicate data in a record.
Example: MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc CAPT.....M566679.....John Blue.....123456.....CBCA CAPT.....M566679.....John Blue.....123456.....CBCA the example above should only display once because of the repeated AccNo and TestCode
Example: MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc CAPT.....M566679.....John Blue.....123456..... CBCA CAPT.....M566679.....John Blue.....123456..... LIPID the example above is just fine because the TestCode is not matching
How do i create a query to accomplish this? someone please help, i've had troube with this for the passed couple weeks . .. .
The school director is going to input a date (by default today) and a subform will display all of the students who started before that date and haven't been closed as of that date. Next to each name, there will be 4 radio buttons (present, absent, holiday, hospitalized), which by default will be set to present. The director will go down the line, only needing to click when a student isn't present. At the end, she'll push a save button and the new records will be added.
My proposed method:
1. When the textbox with date input is changed, the subform (or just form and I put the textbox in the header?) will populate itself with a query based on student names whose corresponding startdate and enddate work with the inputted date.
2. The subform will be in continuous view so that it kinda looks like a data sheet but it has radio buttons rather than just spreadsheet cells.
3. When the save button is pushed, some visual basic code in the background will look at record 1, grab the student name and selected radio button value, and add a new record to the AttendanceRecords table with the name, attendance type, and date. Then the code will move to the 2nd record, rinse and repeat until it goes through all of them.
Questions/problems:
1. At its core, is this the Access way of doing something like this? Is there a simpler, more efficient, or generally smarter way of doing attendance?
2. How is step 3 going to work? I can look up how to add new records to a table, but I'm not sure how to move around from record to record collecting corresponding data.
3. Since the AttendanceRecord table uses studentID and typeID rather than actual names and actual attendance types, what's the easiest way of adding the new records with that data efficiently? I'm thinking of making the RecordSource of the subform some sort of linked up set of tables such that if I'm looking at record 1 and the name in the textbox on the subform is "Bob" I can just reference studentID in the background without needing anything on the form at all with studentID?
I have been searching all day for a solution. This is my first time using Access so a lot of this goes over my head. I'm currently running Access2010. I have a form that I am using for when people take office supplies and we can generate a cost per department. All of the Tables, Query and Reports are set up already and surprising work. My form fields are:
Date Created (automatic current date) Quantity Item Code (Combo Box that shows Item# and Item Description) Initials Department (Combo Box that shows Dept# and Dept Description) RefCode Remarks
I am trying to get it to where when I enter a new record it will autofill with all fields expect "Item Code" from the previous record until I change it.
I have a continuous subform recording the information of various wireless products for an employee embedded in an employee main form. Please see screen shot enclosed.
There is a check box "BYOD/Personal" on the subform. I want to hide a number fields when this check box on independent record is checked. However, if I use the following codes, the changes apply to all records under that employee.
Private Sub BYOD/Personal_AfterUpdate()
If BYOD/Personal.Value = True then Me.Provider.Enabled = False Me.XXX.Enabled = False....etc. Else Me.Provider.Enabled = True Me.XXX.Enabled = True....etc. End If End Sub
How can I just disable those fields on the subform of a specific record when the BYOD/Personal field for that record is checked?
I have a main form "frmDriverNoticeRecord" with 2 subforms, "frmSubDriverNotice" and "frmSubVehicleRecord"..The main form uses "IncidentID" as the PK. Each form stores data in its own table. The tables are related via IncidentID and there is a 1-many relationship between tblIncident (main) and tblDN (sub) and tblVehicle (sub).
I have a form "frmIncidentLog" displaying records in a table view with a few fields for quick reference and to allow selection of a single record for detailed viewing. I created a command button with the intent to open the "frmDriverNoticeRecord" at the current record. The main form and child link fields appear to be linked correctly; i.e. IncidentID on all three. If I open the form manually it opens and I can use it and search and filter as I want. If I try to open it via the command button It opens a small window asking for the IncidenID, when I enter theIncidentID number, it opens the form to the first record every time.Here is the code:
Private Sub comOpenDR_Click()
Dim frmName As String Dim recID As String frmName = "frmDriverNoticeRecord" recID = "[IncidentID]=" & Me![IncidentID] DoCmd.OpenForm frmName, , , recID End Sub
I tried running the the DoCmd.OpenForm command directly from the button using the where condition (in various manners) with the same results.
I need to add two previous record fields together in a query. I have attached a picture to illustrate.
The "StudyYears" filed is the primary key of the table the data is being pulled from. The zero's are placeholders for the sum of the previous record's data.
I'm trying to get a DB to move records simultaneously using a command button. The records are setup with check boxes for example: one column has a check box for 1 Year Ratio the next column has a check box for 2 Year Ratio and the third column has a check box for 3 Year Ratio.
How can i move the information out from 3 Year Ratio into another table and then move 2 Year Ratio check into 3 Year Ratio check box and 1 Year Ratio check into 2 Year Ratio check box by using a command button.
I'm sure the coding is very simple, the question is .... can I build an expression using an IIF statement that checks whether the current field is the same as the same field in the next record? If it is put a 1, if not put a 0. Easy in Excel, not sure about Access??
i would like to check a record exits before a new entry is saved. However i would like to check on three criterias and i am not sure on how to write the if statement.
IF Dcount "CompanyName" & "FirstName" & "Last Name" from "tblcustomer" is the same as "frmCompanyName" & "frmFirstName" & "frmLastName" > 0
I am running a check for checking whether a value already exists in the database table. But the problem is that the control passes right through this check.
It does not give an error too...
If txt_id.Value = DLookup("id", "indicateur", "id=" & txt_id.Value) Then info = MsgBox("Cette 'Id' déjà existe !", vbInformation)
I would like help with a macro that looks to see if a record in my database table exists, then if it doesn't it adds a record with my data, but if the records does exist, it stops the routine and sends a message box to prompt if you want to continue with adding a new record.
The problem that I'm having is that my macro errors out when it doesn't find a record. I just want my macro to just see if a record exists or not. Then I can write my Iff statements based on whether it finds our or not. How do I just have my macro check for an record, then if it doesn't exist tell that it doesn't exist and not stop my macro. Is there some kind of IfExist() function or something?
I need to know how to check if there is a record set matching a name and insert record if there is not one, otherwise just update if a record does match. See code: Code:<%Name = request.form("name")picture = request.form("picture") opencnset rs = Server.CreateObject("ADODB.RecordSet")'if no record matches the nameSQL1= "INSERT INTO background (Name, Picture) VALUES ('" & name & "', '" & picture & "')"'elseSQL1= "UPDATE background SET Picture = '" & picture & "' WHERE Name= '" & name & "'"cn.Execute SQL1closeCN%> Thanks in advance for any help you all can provide!!!! -Chris Gordon
i managed to get checking for duplicate record by ONE field ie member_no
code :
If DCount("*", "runner", "member_no = " & Me.member_no) > 1 Then MsgBox " This member is already exist!" & vbCrLf Me.c_memberid.SetFocus Else DoCmd.CancelEvent End If
how i want to get checking duplicate record by TWO fields ie member_no and run_no ?
