Modules & VBA :: Open In Main Form The Oldest Case Taken From Query
Jul 7, 2015
I need to make a query that will show the oldest cases and then open this one in the main form. It will be possible that there could be several cases with the same date. For example the query runs and the results are 7, how could I get one of those cases in the main form.
The idea is that a person will click on a button called "next case", the query will run and then it will open the case in the main form.
I have a report that tracks scores for our employees. From the report, you can click a button to add a new score in a form or edit an existing score(frmscoretracker). On this form there are two subforms, in a tabbed control to track additional information about the score; what areas were marked down(Trends), and was it a failing score(AutoFail).
When this form opens I have it programmed to only show the subform if there is data in it. The goal being, if I am adding a new score and there is no existing trends or Autofails for this new record, neither subforms will show - I will add an after update even to show either trends or autofail depending on the score recorded. Also, if someone chooses to edit the score, whatever subform with data, will show as well.
When someone clicks to add a new score, opening this main form to a new record, both of the tabs show. However, if the form opens to an existing record, the appropriate tab shows. Here is the code
Code:
Private Sub Form_Open(Cancel As Integer) 'If the subform has a record, the tab is visible, if not, the tab is not visible If Me.frmtrends.Form.Recordset.RecordCount > 0 Or IsNull(Me.Trends) Then Me.Trends.Visible = True Me.TabCtl33.Visible = True Else Me.Trends.Visible = False End If
[code]...
Both tabs are set as not visible in the default settings. Is there something in this code that is triggering then to be visible when there is no record in the main form?
I want to return a value from a separate query and have that show up in a field on my main form. The field name from the query is "Six Month Date" and the query name is "DT6Monthqry". I want to return the value displayed in the"Six Month Date" from the query, matching the CustomerID in the query result to the CustomerID that is being displayed on the main form. The CustomerID is a text field.This is the code I placed in the Control Source property for the field I want to be displayed on the main form:
I have one table containing name of restaurant with its address etc. Then i created another table to list out the restaurant workers names and details. Just as an example,
Table:Restaurant Restaurant name Address line 1 Address line 2 Restaurant # Website
Table:StaffContact Staff Role Name speciality email phone
I have the main form that has all the restaurant details only. And i have another form containing the Staff information. Please note the two table have a relation and it works well.
Now to make it user friendly(basically easier for the lazy ones), I dragged the staff contact form on to my main form and displayed it as a datasheet(basically a sub form).
Now, my boss does not want users to add/delete on this sub form(datasheet). So,he wants me to create buttons to open new record of staff for each restaurant(new form)
My issue is with opening a new record to enter a new person to the staff list and give them a role as well in form view.The new form has
So i ran a Macro, with open form with Where condition
I have a main form with a combo which lists all forms in my database. There is also a button which open all the forms depending on this which I will choose from my combo.
Private Sub Command2_Click() DoCmd.OpenForm Me.cmbforms End Sub
Is there any way to open the forms in a subform in my main form?
Hi I need code to open TeacherDetailFrm form Main form.
I have TeacherStudentSubFr*m on the Mainform, FirstNameTxt on the TeacherStudentSubFr*m, and TeacherDetailFrm. I need code to open the TeacherDetailFrm By double click on the FirstNameTxt.
I use this code but no data come and query dialog box come.
Private Sub FirstNameTxt_DblCli*ck(Cancel As Integer) Dim DocSubForm As String Dim DocText As String DocSubForm = "TeacherDetailFrm" 'DocText = "[Result]=" & "'" & Me![result] & "'" DoCmd.OpenForm DocSubForm, , , "[ FirstNameFld ]=forms![TeacherStudentSubFrm]![ FirstNameTxt]"
End Sub
I like to upload the file but no way in this forum
I have a database that has a main form, and from that main form there are buttons that open their respected forms. I am curious as to how I control the size of all of my forms. They are all pop-ups at the moment. It all works fine on my monitor, but when opened on a smaller monitor it is too big and I can't even see the bottom of the screen. The main form is set to open automatically after the user logs into the database and it opens to fit the screen. also, all of my forms are modals.
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 Query which has dates in multiple columns. I need to know if there is a formula that will allow me to display the oldest date and the newest date?
Below is the output of my Query in Excel. I need the output to have two additional columns: Beginning Date and End Date. Based on the example below, I would need the beginning date to show 6/21 and the end date column to show 6/23 so when I export to excel, it shows these two dates. In Excel, there is a min/max formula that can do this but it looks like the min/max in Access only displays the min/max data? Unfortunately, creating this formula is not an option after the fact. Secondly, I need the word "DIRECTION" to show in it's own separate column in the in the query. I don't know if I can do this without adding another table to the Query or if I need can just add a column in design view and put something in the criteria that will do this.
I have included an example of my current output and what I need the output to read.
Current Output Sunday In Out Monday In Out Tuesday In Out
I'm trying to open a folder based on a BIN nr. This folder could be in a main folder that has diferent subfolder. As there where differnt naming used to create the folderes, one of the things they have always is the BIN nr. It alwasy start with a unique number and maybe I could use it to scan the subfolders and open that one that the BIN nr is equal as in the field BIN.
Now we have serveral 1000th of folders and finding them takes time.
I am creating an events database. In my main form, I have all the details of a single event (billing code, fees, customer, support personnel, etc.). Then, I have a subform (form view) that lists each of the other services which share an event ID (this ID comes from a separate, unrelated events management suite). In the subform, I display the most-commonly-edited fields so that they are editable without navigating to a different record.
However, there will be instances where it becomes necessary to navigate to one of the records listed in the subform. I would like to make this a quick process by just clicking on the primary key in the subform, and have the corresponding record appear in the main form.
In my searches I've found a common suggestion to use a DoCmd.OpenForm method where the form opens to that specific record -- in effect filtering by the primary key number. I don't prefer this method because it disables the ability to navigate to other records using the previous, next, find records, (etc.) buttons without closing and re-opening the form.
Is there a way to make the main form 'jump' to a specific record by clicking on a control in the subform, without limiting the ability to navigate in the main form?
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 'main' table with a Project_Number that links all the data in my db together. I have another table that uses that Project_Number as a lookup field to connect that tables data to the main data. I created a 'main' form that has the ability to enter data for the 'main' table. I want to be able to press a button and have the second tables form pop up and add that that specific Project_Number. I added the button and went through the wizard process. I then added the linking info through the builder. It works fine if there is already data entered for the project_number in that specific field. but if the field is empty, the popup window doesn't recognize a project_number and doesn't add it to that record. below is what I am using. The project_number in the 'main' table is text and the Project_Number in the 2nd table in a number since it is a lookup field.
Private Sub CongressionalDistrictCmd_Click() On Error GoTo Err_CongressionalDistrictCmd_Click Dim stDocName As String
I have a subform that is used in multiple other forms. I wish to have a command button that will close whichever main form the subform is in. Is there some way I can make an if statement that is something like the following?
If (main form = A) then Close (form A) ElseIf (main form = B) then Close (form B) etc. End if
Unfortunately the button has to be in the subform, not the main form (which would be easier). The purpose of the button is to delete the current records that are open and then close the form. The catch is that the subform is the parent record to the main form, so the command has to originate from the subform so that the cascade delete works properly.
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 need to hide my subform on load or open of the main form but am experiencing some weird results. I have tried putting Me.SubForm.Form.Visible = False in both the on open and the on load of the main form and it works only if the tables on which the subform have records in them. If the tables for the subforms are empty I get an error 2467 telling me that "The expression you entered refers to an object that is closed or doesn't exist." I find this strange because from all I can find the subform loads first.
How can I hide the subform when the main form opens no matter what records exist or don't?
I have a form - purchase, and a subform purchase detail they are linked with a field "puid"..I need the dmax to give me the maximum auto number field "pdid" on the subform in a textbox on the mainform.
I'm using form with subform. Main form header contain a combobox. I need to check the combobox is null or not before update a subform filed. If combobox is null then its back to Main form combobox from subform. The code below that I'm trying to:
Code: Private Sub AssignTo_BeforeUpdate(Cancel As Integer) If IsNull(Forms!frmDepartmentReview!cboDepartment) Then MsgBox "You must select Department first", vbInformation Forms![frmDepartmentReview]![cboDepartment].SetFocus End If End Sub
Then I'm getting 2110 run time error
Access can't move the focus to the control cboDepartment.
I have written code to look up a value in a table that then enables or disables a subform in my main form. The code works, but I know it is now as efficient as it can be. The main problem is that I have multiple values that determine if the subform should be enabled or disabled. I would like to use an IN statement but I'm pretty sure this doesn't work for Dlookup. Below is an example of the code I currently have:
Code: Sub enablecontrols(setting As Boolean) Inv_subform.Enabled = setting End Sub Private Sub Form_Current()
[Code] ....
Like I said, this works fine, but I am concerned if I need to add more items to look up and the stability of the code in general.
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 an unbound mainform and an unbound subform(datasheet). The source of the subform is a query which is dynamic(I have many queries with diffrent columns). the subform source is change using a combo box selection. I have text boxes in my mainform..how do I get the data from subform to the text boxes of the mainform?
I'm developing in Access 2007 and created a runtime version.
When the user clicks the X to close the runtime application, the main form's Load event fires. Any clues as to why this might be? Some of the code is based on other forms that do close, so of course errors start flying. Note, this is only in the runtime version; the accdb file works just fine.
My current workaround is to put the offending code into the forms On Current event, which I'm able to do in this context.
Is it wrong to think that a forms On_Load event shouldn't be firing when the application is closing?
I have a subform that displays doubles in a textbox as fixed with 4 decimal places as default. I have been trying to change the number of decimal places based on the value in a textbox on the main form. I have tried this:
where [Final PCT] is the textbox on the subform containing the decimal number and Numdecs is the textbox on the main form containing the number of decimals I want to use. I want to implement this Form_Current sub.
I have a modal form which has a single record which is then linked to a sub-form on the same modal form. This all works fine and shows the relevant record and sub records but I want the modal form main record to change dependant on the record selected in the subform (which is basically order item history.
I found some code on another site which seemed to work for others but for me I am getting Run-time error 2465 and it doesn't like my reference to 'Me' - is this because it is a modal form?The code I am using is:
Public Function GotoRecord(RecordID As Long) Dim rst As DAO.Recordset Dim strCriteria As String Set rst = Me.RecordsetClone strCriteria = "ID = " & RecordID rst.FindFirst strCriteria If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
