I am designing a db for a dental clinic. I created 3 tables as follows:
1.T_Patients (P_Code(PK) and other fields)
2.T_Diagnosis (P_Code(Fk), ToothRecId (PK) and 32 text fields for 32 tooth named (UR1 to UR8, UL1- UL8 for Upper left / right Arch and LR1 to LR8, LL1 to LL8 for Lower left / right Arch)
3.T_Pmts (P_Code (FK), PmtRecId(PK) and other fields
I created one main form based on T_Patients tbl and similarly another 2 forms from other 2 tables. In the main form I placed 1 sub-form and 2nd sub-form. Bothe the sub-forms are Master / Child relationship on form over P_Code field in the main form.
Single character length has used for all the 32 text fields in table T_Diagnosis. So only one single B, C, D, F, M could be entered.
Normally we search patient’s profile by patient’s code or by name that displays in the main form. But the client needs to search patient’s record by tooth criteria.
There may many tooth fields in the first sub-form that are null and only some of them with any one of the B, C, D, F, M character. Bcz there is very rare chances of damaging all 32 tooth of the patient.
Now my clients want to search based on the data which are B, C, D, F, M characters. Means once he types C (stands for Cavity) in a search text box then it should bring all the sub-form records that hold value C in any of the 32 fields.
1.How can I do this when the search based on SUB-FORM and not over the main form?
2.The entered criteria value might by in other field also. Like say if entered C then this value may be in any of the 32 fields. So how it would bring all records?
I am confused how to do it. I attached a pic of my form for your ready ref. :confused:
I would really appreciate if somebody could help me.
I have only 1 table that im working with I simply want to have a form that runs a search on one of the fields and returns the results.
This is what I have:
1 Table
ID : Autonumber
ProblemType : Text Technician : Text
I want to create a form that has a main form with a text field that i can input a string of text and run a search on the problemtype field. And return all the results in the subform with based on keyword that was inputed in the form above. I want the results to display Problem type and Technician fields. What is the best what to do this?
I am currently working on a project in access 2010 and I am having a search for record dilemma. I have a main form that has a subform in it under a tab control. I place a search box at the top of the form so that user could select either the ID, the internal case number, and/or the reviewer name in the text boxes to search the record.
I have tried the using the search for record macros and the convert the macros to VBA and possibly write so extra coding for calling the subform into the search but nothing seems to work in getting the search in sync between the main form an the subform. VBA code that will look for same record in both forms.
The reason for the tab control is because depending on the data entered the users switch between mid section data about case and so one set of info goes into one tbl and the other goes into another, but the subform that I am referring to in this question is being generated from the same tbl as the main form.
I have two tables called "tblFundingBodies" and "tblGrants". I have a form that allows me to view all the records for each of the funding bodies. By this I mean, it shows information on a funding body but to see information on the next funding body, I must click on the next record button. Just wanted to clarify that it isn't in the form of a table.
This form also has a subform in the same format that shows each of the grants that funding body has provided. When I select the next record from the main form, it will show me the grants from the company in the next record. The field that has the funding body is a text box.
I want to be able to add a seach facility to the main form that allows me to search for a funding body and get back exactly what I have described above, but just the details on that funding body/grants. In other words, I want the form view of the funding body I search for with the sub form of the grants from that funding body, not in the form of a table where you can see them all at once, I want to be able to click through each grant in the sub form.
Input box Search problem. I have been using the following to find a record in my frmMain using an Input Box:
Private Sub cmdClientIdSearch_Click() 'Searches for Client by ID number Dim rs As Object Dim strCriteria As String strCriteria = InputBox("Please enter Client ID" & vbCr & "Do not type leading zeros")
[Code] ....
It works great if I run it from a command button from frmMain. But, if frmMain is open and I run it from a command button on my switchboard, I get the following error message:
Run Time error 7951. You entered an expression that has an invalid reference to the Recordset Clone Property. I tried inserting a command to open frmMain in the first line, even before the two Dim statements, but I get the same result.
I have a database with a Main Menu Form, containing a Button that loads my main data entry form. When the Button is Clicked portions of the data entry form that is loading shows through the Main Form Background (e.g. portions of the navigation bars, and portions of the boarder on the form that is loading.)
I have a subform which makes a change to a field on the main form. When focus is returned to the main form, the BeforeUpdate and AfterUpdate events fire. Why? I thought from the form's perspective, the subform is just another control.
BTW, I get the same behavior if I modify the field from within the Exit event of the subform control.
In either case, the main form's Dirty event is NOT triggered.
I'm trying to search a for string within a subform to find information stored on the mainform to which the particular subform belongs.
The problem is that the subform is generated from a query which uses a number from the main form to generate.
So the subform record is only generated when the correct mainform record associated with it is loaded.
Now to solve my problem I've made a new query that brings up ALL the results that could be generated by the main form and from that I can search to find my search term I'm after and read off the ID number to tie it back to the mainform.
But all of this is done manually, I want a way to do all this using VBA in a way that the user can't edit any records as they are doing it.
I’m hoping someone can help a very confused newbie :confused:
While I have knowledge in using MSAccess I can honestly say I am well and truly stuck. I have searched the forum, but cannot find anything to help me out … if there is anything I am sorry for re-posting. While there may be helpful responses, they mostly involve code, which I do not know.
I have a Main Form (frmPreApprovedTravel) and numerous Subforms (sfrmFlightRail, sfrmHotel, etc). I have designed my Main Form with tabs and each tab contains staff details, flight, hotel, taxi, etc information.
My problem is that when I go to the next record in my Main Form I want the Subforms (all of them) to go to the next record as well. I know this is possible but having tried to figure it out myself for the last 2 hours, but know I’m still missing something!
I would appreciate any help and thank you in advance for the help and taking the time to read my problem.
OK.... This is a DB I've been playing with a while... Got a few things fixed and working.... But of course more problems came up. This is a DB for tracking time spent on a project. This was all it was at first... Then I decided to add invoicing and got into trouble :) I have one main problem now and can't decide the best way to fix it.... Other then starting again and redesigning the DB. I attached a copy... Easier to see the problem that way. From the main menu, click on "Open Client Billing"......From there, select a record on the subform and click "View Unbilled Time" From this form you will check a box to "Export" that time to create the invoice. And here is the problem. For example, if you click one out of two records to export it shows your record and creates the invoice. BUT... then if you close out and open it again to add the other record creating another invoice the previous record is ALSO in the new invoice! So.... The records in the subform are not getting tied to the invoice number. Anyone want to make some suggestions on how the best way to correct my problem would be?
I am having issues with my subform loading all records from the main table that its linked with. My main table has 8,849 records that fall Group 1 but only 8,831 records are showing up in my subform. I need to figure out how I can fix this. By a SQL code or VB code.
Hello. The problem I am having is related to two tables. I have a table called Void and a table called Panel. A panel can have multiple voids and a void is assigned to only panel (one-to-many). In the Panel table, I set an autoincrementing number(SprayPanelId) for the pK. In the Void table, I set an autoincrementing number (VoidId) for the pk. I place the SprayPanelId into the Void table as a foreign key (same name in the void table). The problem I have is in my forms. I created a Panel form with a button to open a subform for data entry on the voids related to the panel in a new window. Data can be entered and is properly saved in the Void table. I open the Void table in a datasheet view under the table section and see it is properly there. I can run queries where Void.SprayPanelId = Panel.SprayPanelId and it returns the correct void with the correct Panel. The problem becomes when I open my void subform, it no longer displays that record associated with that panel. however, the record is there in my void table. Any thoughts? Do I need a filter perhaps, run any sql commands? Thanks in advance, Kevin
I am trying to create a command button that clears all the records in the Sub form in the CURRENT RECORD OF THE MAIN FORM. I know this sounds a little bit confusing, but I hope you understand!
The main form is: frmOrder2 The subform is: subfrmItemOrder
The fields in the subfrm that has to be cleared are:
I have search filter form that when I click the search button is passing a value in OpenArgs to the Main Form on open event. The OpenArgs is changing the recordsource of the subform on the main form appropriately. However, the main form is referencing all of the records, not just the ones that are int eh subform recordsource. My link master and child fields are correct. There is a 1:N relationship between my mainform table and subform table.
For example, my my subform, the recordsource is diplaying 2 records where the Status = BLUE, however, the mainform (Projects) is showing ALL records even where the status isn't blue. So when I scroll through the mainform records, if the project isn't status = blue, the status subform shows a blank record. I only want the Projects where the status is blue.
I have created a database in microsoft access 2010 to show invoices for different customers in different countries. In doing so, I created using a two subforms in a main form. I have used the "country name" to link the subforms to the main form. When I enter new records into the subforms for a specific country, I realized that a duplicate record of an old record are being created in the subforms. What can I do to prevent this from happening? I tried to change the query link between the main form and subform to "invoice number" but the same problem has occurred.
I have a form with 22 subforms (in about 11 tabs). Each one of these subforms has a controlling field in the main form (table) that dictates if the associated subform needs to comply with data entry rules or not.
For example: Field in main form cboMed (Yes; No) Subform: frmSubMeds has a list of medications
I want to be able to loop through all the records of the subform and determine if they are complete.
This check is run from a command button; that should also consider the value in the combo box. i.e. Check only if the combo has a value of "Yes"
Since i have 22 subforms and have to run the check on everyone of them, i have used the tag property to check if the record is blank or not. This is what i have so far
Code: For Each ctl In Me.Controls Select Case ctl.Properties("ControlType") Case acSubform ' only look for subforms If ctl.Enabled Then If Not (IsNull(ctl.Properties("SourceObject"))) Then 'if it is a subform type
[Code] ....
The problem with this is that it only looks for the selected record in the subform and doesnt look for ALL the records.
I have a main form and four subforms all of which were created from the tables without queries. The main form pulls employees personal data (never changes). The subforms pull Events attended, Lodging at the event, Family that attended the event and Employee minutia which changes per event. I'd like to have a button on the main form that changes the records in view for all of the subforms at once. This way it is easier for the users and I know that I am view all the pertainent info for event A instead of viewing event A's lodging info with event B's family info.
I have an existing Main form that has a sub form that the user uses to enter multiple records into a table....it works fine EXCEPT that I need to make it even easier and more intuitive and add a lot of labels. Basically the user selects items from a drop down list that adds items to a Work Order. I need to add some labels to the form to make it more descriptive for the user.
So, what I want to add multiple records using a single main form.
Is is possible to?:
1. simply turn the subform into a single main form? Can this be done by using a Command button or something similar?
2. copy all of the controls etc from the sub form into a new main form and have it all work nicely?
In my database main form with subform. subform have query as recordsource.total of one of field in subform shown on main form. all is ok and show total correctly but when subform have no records then total field on main form shows #error. How to convert this value either into null string or zero(0).
How can I make certain my user enters records on a subform before attempting to save the main form? Right now they can completely ignore the subform before saving the record.The Main form has business address, etc. on it. the subform is bound to a join table that lists the multiple categories, subcategories and sector the business is listed in for a directory.
I already have my fields set to required at the table level in the join table, and have some existing VBA in both my subform (to update edited date) and my form (to validate empty records where a certain condition is met) but that's not the issue...
How do I focus the user to enter a record on the subform to the point where they are forced to enter something and complete the subform before the record is updated.
I have developed a database but have had difficulty with sorting data within subform of a main form. The subform displays the related tasks that correspond with main form that has been selected. Within the main form I have also created a text field that defines a particular sorting sequence of the tasks found within the subform which is titled Task Sequence. What I would like to do, is use the Task Sequence field to sort the order of tasks within subform. I have tried: IIf([ID] Is Null, 0, Val([Task Sequence])) within the Advanced filter/sort but either it shows only the first task defined in the Task Sequence or it wants to filter the main form and not the subform.
Hello Friends, I had a problem with my database although the problem is very easy because I am out of touch I am not able to solve it. I am attaching my databases along with this thread. I want that if the user enter First, Last or Inumber in a text field in the Form "search". After pressing the button ‘search’ a the form ‘search_results’ opens and displays all data records that match to the entered parameters. By clicking on the data record the form ‘candidate’ opens with this specific record and the form ‘search_results’ is closed. I would be happy if someone help me. Thanks
I'm trying to incorporate a combo box in my form to navigate through records. The plan is to select a piece of equipment listed in the combobox, and that record will then be displayed as an "after update" action.
However, I have tried through coding I have tried before and utilising the wizard to create this combo-box, but each time I get a "Complie Error - Can't find project or library".
The section of code is shown below:
Private Sub Combo227_AfterUpdate() ' Find the record that matches the control. Dim rs As Object
Set rs = Me.Recordset.Clone rs.FindFirst "[EquipmentID] = " & Str(Nz(Me![Combo227], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub
I have several forms in a navigation form. On all the forms, the little search bar at the bottom shows # of 103 records. On my navigation form it shows 1 of 1 and won't allow me to search.It would seem that the search bar is only looking at the current form and not at the records.
