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
The asset managment system I have been developing is due to go live next week and I am really struggling with the data entry and search forms.
I have attached a picture of the main form (and outlined each subform) to give you an idea of my project. The attached form is actually a Main Equipment Type Detail form with a child subform AssetMain which has a number of child subforms i.e. Asset Details, Purchase Details etc.
When a user clicks to add a new asset a popup form asks them to select an equipment type i.e. PC. When they click it opens the attached form. Their selection filters the equipment type detail values in the combo i.e. Laptop, Tower, Server etc. When a value is then selected in the combo you can then add an asset for that equipment type detail. (One Equip Type > Many Equip Type Detail > Many Assets)
1st Problem) I have set the Asset Main subform to allow additions only. When the whole form opens, the Equipment Type combo (at the top) does not display a value but does actually contain the first value. How do I force my user's to select a value in the combo before it actually filters the child subform?
2nd Problem) The Asset Main Subform (filtered by selected Equip Type) should only be used for data entry. I set the data entry property of the form to True but when the value in the Equip type combo changes, it displays all the relating records in the subform. My only solution so far is to stick
Subform.DataEntry = True in the Equip Type combo's after update event. However, this doesnt look to good as it flashes up with the data and then displays a blank record. Any ideas?
3rd problem) Due to the fact that I am using a number of subforms, my tab order is such that values are entered for the main form (i.e. ID, Condition) then subforms (i.e. manufacturer) then back to the main form (i.e. allocated to). This means that: a) I am having problems validating the record beforeinsert as I have had to change the required property to "no" b) Once the user has tabbed through all the forms what is the best way to go to a new record? Alternatively if I add a new record button I need to check that the necessary fields have been filled in (on all of the forms/subforms).
and finally the 4th) I have a search form that queries the assets. The search results are displayed in a list box. When you dblclick the list box I need to show the asset details. Question: Can I use the above form to do this? My only problem is that I need to open the AssetMain form which is a sub form in the above. If I try and use the existing form it will not open as many of the controls refer to the form as a subform. Do you think I need to create a separate form for showing the data to entering it?
Would really appreciate some help (not that you havent heard that before :) ) My head is going to pop in a minute!!!
I want to limit the numer of times a record can be entere into a table. After a record has been entered 3 times, I want an error message or validation rule to tell me it can't be done.
How to limit input data on the same month for each patient? The field is a data/text box on a subform
I found this code on Form Current Event!:
Code: Private Sub Form_Current() Dim intMaxNumRecs as Integer intMaxNumRecs = 5 'Max Number of Records to Allow If Me.NewRecord Then With Me.RecordsetClone If .RecordCount > 0 Then .MoveLast: .MoveFirst If .RecordCount >= intMaxNumRecs Then MsgBox "Can't add more than " & intMaxNumRecs & " records in the demo database!" .MoveLast Me.Bookmark = .Bookmark End If End If End With End If End Sub
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.
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.
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?
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?
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".
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 have an unbound form on that form I want to put three sub forms one on a products table the other on a course start dates table and the link table that joins the other two together. all three are related to each other with Pk/FK links.
When I try to link them it says you cannot link items on an unbound form.
I have two subforms on my main form. Both subforms are very similar and comprise two fields each. Both have an After Update event set on one field to enter the current date into the other field.
In one form I can update a record, press F9 and everything is smooth - I can proceed to somewhere else.
In the second form, if I update a record, the date is entered in the second field, but with black background, and I have to focus on the field, press Enter and then Esc (in the right order) to be able to proceed. I have tried many things to stop this as it is slowing data entry (just when I am starting to feel abit sleep deprived with an early start ahead).
Hello All, In my table, I have some records that are duplicated (aside from the primary key). How can I select only one record from each set of duplicated entries? (There may be more than 2 of each of the duplicated records, but I only need to pull one of them...doesn't matter which one.)
i.e...Like a 'Find Duplicates' query, but only returning the top 1 primary key from each duplicated set.
I have a form that opens up and fills in all of the Orders Table when it opens.
I then have a subform that is used to fill in the order details.
Currently if they open the form and then close it, it creates a record in the order table. I want to be able to delete this record if no information has been filled into the subform?
I have a quick question on how to enter the current date in a record automatically when the record is created.
Here is the situation. Say I have a Products table that lists the products that have come into the inventory. There is a field that denotes the day the product was introduced into the inventory. So ideally when the user creates a new record for the product, I would like this field to be populated automatically with the actual date that this filed was created by the user. Note that in future the user may edit the product (i.e other fields in this record), but the entry_date field should not be altered from what it was the first time is was created.
Is there a way I can do this? Any help much appreciated. thanks.
I have a problem that is probably very straightforward to you Access experts.
A brief description of the salient parts of my setup are given as numbered paras below.
1... I have 2 tables: Jobs and Sessions in a defined 1 to many relationship.
2... I have defined a form in parent-child format with the child (session) records displayed in single line (continuous) mode.
3... The above works as intended, ie as different jobs are selected (via combobox or bespoke navigation arrows), so the relevant session lines appear. I have removed both record selectors and default navigation buttons from both form and subform.
4... Because all but one (a simple text description) fields are auto or calculated, I do not want the user (Yes ... it's me ... but It could be someone else ... if I drop dead) to attempt to input via the form, since it causes problems for some of the calculated fields. :(
QUESTION A. How can I suppress the record entry part (bottom line) of my session one-liners subform? (The problem doesn't arise with the main job page-mode form, since the removal of the default navigation makes it impossible to add a job via the form).
QUESTION B. What code do I need to create a new session for a defined job via a simple dialog box or bespoke form which requires only two input parameters: jobname and sessiondescription. (All other fields are calculated automatically from today's date and jobname)
5... I currently perform input using the jobs table (ie selecting the relevant job), expanding the (sub)sessions table and then adding a session description ... but this is not so user-friendly!
I would appreciate any help and/or relevant comments.
Could anyone of you give me some sugguestions on designing a nice/efficent Data Entry interface (Form design) for adding new records?:
I have bound a Form with a table and linked all the textbox with the relevant fields in the table. I created a add new record button through the wizard provided by MS-Access as: DoCmd.GoToRecord,, acNewRec.
While, each time when I opened that Data Entry Interface, the current always pointed to the first one rather than leaving the blank field for data entry. Even I changed the value in those textbox, the system didn't create a new record in the table at all, only modifying the first record.
I know how to write the VBA code to open table and add new records by retrieving data from the Form interface, but I wonder whether there is more efficent solution by combining the default function/facilities from MS-Access itself and some VBA code. Also, it can have some validation before storing data into the tables.
I am coding in VBA and use Microsoft Access as the front end. My question is : I have a table(tblStudent) which has two fields Student Name and Gender.
I want to add multiple records in this table through a form at a button click....(Say I want to add least 5 students at a time) So, I have 5 textboxes for student name, 5 textboxes for Gender and an Add button. How do I so this programmatically?
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.
On a form to enter some new client info, I want users to be able to select their UserID from a combo box for future reference to other users (ie; Who made these notes?). For various reasons I want them to be able to add their UserID to the combo list for future selection if its not already on there.
There is a table set up for users, simply called tblUsers, with a single field, UserID. A query from this sorts the list alphabetically, and the combo uses this query to populate its list - qryUserID.
Users can currently select from a list or write their own UserID in the box, however when they write their own ID it doesn't get saved.
I don't need any message boxes or checking, just add it and move on kind of thing. It doesn't need to refresh the list immediately, as the user moves on swiftly once completing 2 more fields.
