Modules & VBA :: Go To A Specific Record In Open Form
Mar 27, 2014
I have a form that is opened as hidden when my startup form loads. Data gets entered into it from other forms so whenever I switch records the hidden form needs to be opened to the matching record.
To accomplish this, I have been using the DoCmd.OpenForm in the OnCurrent event with the WHERE clause matching the PK/FK. I have had no luck with the SearchFor method.
It seems silly to have to keep reopening a form to go to a specific record when the form is always open already. Plus, I am wondering if it is affecting the forms' load times.
Is there a better way to do this (there must be)?
View Replies
ADVERTISEMENT
Sep 4, 2014
I am using a datasheet view with dbl click code to open a form to a selected record. I was able to use pbaldy's code and it worked perfectly.
DoCmd.OpenForm "Asset Status", , , "[Project Number] = '" & Me.Project_Number & " ' "
Then I realized I really want to be able to go to other records after I have gone to this form and tried this:
Dim rs As Object
Dim lngBookmark As Long
'set variable to the current record
lngBookmark = Me.Project_Number
'open new form
[code]....
But to no avail. Project_Number is a text field.
View 12 Replies
View Related
Oct 8, 2014
Is it possible to use VBA or macros to open a table to a specific record?
I know how to open a table in database view using a command button. I'd like to have it open to or automatically scroll to and select a specific record, based on the content of the form. This way I don't have to spend time scrolling through the whole table to try and find the desired record.
View 6 Replies
View Related
Feb 2, 2005
Hi,
I want to open a new form which is similar to the form it will be opened from. Only difference is the new form I want to open is designed for printing purposes.
I'd like to access the this new form by hitting a button that opens the print form for a user given order number. Any ideas if this is possible? Thanks
View 1 Replies
View Related
Mar 7, 2006
I am trying to open a single form with a single subform with a command button. I would like to open the form with the subform on a specific record but I am not entirely sure how to do this, I was wondering if someone had any advice that could help.
View 2 Replies
View Related
Jan 26, 2014
1. I have created a button in a switchboard with embedded macro:-
Close Window
OpenForm (Form name: frmCustomerFind)
2. I have created a form "frmCustomerFind), in which I can select a customer name in a combo box named "CustomerName".
a. The row source: SELECT [tblCustomer].[CustomerID],[tblCustomer].
[CustomerName] FROM tblCustomer ORDER BY [CustomerName];
b. After Update Macro:
Open Form (Form name: frmCustomer)
SearchForRecord (Object Type: Form Object Name: frmCustomer
Record: First Where Condition = "[CustomerID]=" & Me.CustomerID
At the end, the form "frmCustomer" does open but not go to the record I want. It goes to the first record instead. (The frmCustomer has the "CustomerID" and "CustomerName" fields).
View 1 Replies
View Related
Mar 31, 2015
Need to open a form to a specific record.Ive done the command button wizard that opens the form to a specific record but but that brings up the form with the filtered button showing.I've tried to put the formula into the filtered section but that doesn't work.I just want to be able to open the form to the specif record and then be able to navigate to other records if required (without aving to press the filtered button)
View 1 Replies
View Related
Feb 9, 2015
Currently, I have a form with tabbed pages in it. The second tab, I have a subform inserted into it. I have a completely separate form that looks through records. I want to be able to click on a record from the separate form and open the Main tabbed form, to the second tabbed page, to a specific record. Currently I have:
Private Sub Form_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frm_Main_Data_Entry"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![frm_Main_Data_Entry]![pgAgreement].SetFocus
End Sub
Which opens up to the tabbed page just fine. but I can not figure out how to get to a specific record.
Also, the subform in the tabbed page, doesn't have the same controls as the Main tabbed Form itself, so when I add something like:
strCriteria = "Agreement_Number = " & Me.Agreement_Number
it won't work because it is not reading from the subform in the tab, rather its reading from the main form controls.
View 1 Replies
View Related
Dec 31, 2014
I have a form that lists a number of orders (list box).
I have another form that has customer details as the main form and the order as a sub form and then another sub form for items....
How can I open the sub form to a specific record? ie I want to view order 14?
View 4 Replies
View Related
Nov 5, 2013
No I know this one has been done, and I have a form with a button that this works on. Here is my problem. I have a list of parts in a continuous form, I added a button to open that specific record in a detail view and it works just fine. I used an open form macro and put in the criteria
="[Description]=" & "'" & [Description] & "'"
I have tried to use this similar procedure for another form, it however has two subforms in it and I get a data type mismatch in criteria exception error. I suspect that it has something to do with the subforms but am not sure.
View 13 Replies
View Related
Aug 20, 2013
I have been trying for weeks to get my login form to open up to a specific record on the mainform. I have built a form out of my Employee tbl (mainform). The table stores (ID,Name, password, EmpNumber ect.) I built a subform in that allows the user to type in the number of overtime worked on a paticular day. I have disabled the navigation on the main form so users can't advance to the next user but have enable the natigation on the subform so a particular user can advance to the next week of available overtime to input data.The goal is to get user login form to display a particular record on the main form and open a different form in the user is a supervisor.
Option Compare Database
Private intLogonAttempts As Integer
Private Sub cmdExit_Click()
DoCmd.Quit
End Sub
Private Sub cmdLogin_Click()
[code]...
'If User Enters incorrect password 3 times database will shutdown
intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If
End Sub
View 1 Replies
View Related
Jan 20, 2015
I'm wanting to open a record in another database, below is the code that opens the form to the correct record in the DB I want to open.
Code:
Private Sub btnDetail_Click()
DoCmd.OpenForm "fJob", , , , , , Me.Name
Forms![fjob]![txtJobNumber] = Forms![fJobAlphabetic]![fJobAlphabeticSub].Form![JobNumber]
End Sub
View 2 Replies
View Related
Jun 9, 2010
I have two tables and two separate forms. [ID] is the link between them. (I cannot do a subform in this case).
Form1 displays 3 records
Have control button that goes to Form2 where [id] = NumID (NumID is "long" variable)
Have control button on Form2 to go back to Form1. It needs to go to the same record it came from. I don't want use a where clause (e.g., where [id] = [id]). This works, but the form1 is now filtered. I need all records available in Form1, but I want to go to the record it was on when the button was clicked to go to form1
Example:
Form1, record1, ID=30
Form1,record2,ID = 35
Form1,record3,ID = 40
Select record3,ID=40, then click on button to go to form2 (has where clause where ID=40).
Form2 is filtered and is correct.
Click on button to go back to form1. No Filter. It goes to 1st record (record1,ID=30). I need all records displayed, but need to be on record2,ID=35.
View 4 Replies
View Related
Apr 10, 2013
I have created a Datasheet Form which when opens shows lots of records. I was looking to see if there was a way that if i clicked on a specific field name within a record it would open a new form with all the information of that record only.
View 5 Replies
View Related
Jul 16, 2012
I have two forms. One is a datasheet. One is a form with a default view of Single Form (which contains combo boxes).I want to have the user select (click) on a record in the datasheet and have the second form open to that record. The datasheet form acts as an advanced search on two title fields.I tried the open form macro but I dont know how to have to second form open to a specific record.
View 9 Replies
View Related
Feb 24, 2015
I have a from that has information icons that opens a separate form to support users make the right choice on a form. With each information form users are able to choose "Yes" or "No" and then it returns them back to the form they started. I am having trouble setting the focus to the control the user original left the form from.
Form A
Control 1
Control 2
Control 3
Form B
Information for Control 1
Form C
Information for Control 2
Form D
Information for Control 3
For Forms B - C I would like the form to close, open Form A and then set focus to the control that the form was providing information for.For example, if you are on Form C and you make a decision, I want Form C to close, Form A to open and focus to be set to Control 2.
Code:
Private Sub image5_Click()
Dim sWHERE As String
Criminal_Background.Value = "Yes"
[code]...
View 8 Replies
View Related
Nov 28, 2011
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.
View 1 Replies
View Related
May 27, 2015
We have an access database which has a form that has a list box that details particular records. See attach "list box".
What I would like to do is double click on the record and this would look in a powerpoint folder (see attach "path") and open the presentation if there is a match to the value in Listbox Column 5 i.e. Tool Number.
I can get it to open the presentation if I include the tool number in the path but I cannot seem to be able to get it to compare the values and only open if there is a match.
View 4 Replies
View Related
Jul 16, 2015
I was wondering if there is a code for Next and Previous record in a form but for a specific value.
Example: I have a customer that has a specific ID and that customer has a list of orders that all contain that ID. When I open a form for orders I would like to be able by using Next and Previous record buttons to be able that was to move only through records of that customer not the whole list of orders for every customer.
View 1 Replies
View Related
Nov 21, 2013
I have a table that has two forms linked to it - one is the Main Form which is used to input the data for the bulk of entries, however on some entries there will be some additional data required which the other form (lets call it Time Form) takes care of.
What I would like to do is have a button on the Main Form that opens the Time Form, and then go to the specific record that is open on the Main Form;I have an ID field (Autonumber + Primary Key) which displays in a text box on the main form, and it would provide the number of the record for the second form to open.
View 7 Replies
View Related
Dec 4, 2013
How to do an UPDATE using VBA on a form to update a specific record on the table using an unbound field on the form to filter the update.
Every time the code runs, it tells me: Run-time error '3144': Syntax error in UPDATE statement and takes me to the "CurrentDb.Execute strSQL, dbFailOnError" line at the end of my sample below.
Here's my code:
Dim strSQL As String
Dim strCriteria As String
strSQL = ""
strSQL = strSQL & " UPDATE [tblTicket] SET"
strSQL = strSQL & " ([UpdatedBy], [AssignedTo], [Requestor], [Dept])"
strSQL = strSQL & " Values"
strSQL = strSQL & " ('" & unbEnteredBy & "','" & cmbAssignedTo & "','" & cmbRequestor & "','" & cmbDepartment & "')"
strSQL = strSQL & "Where [tblTicket]![DateTimeOpened] = #" & FORMS!frmTicketTracker.unbDateTimeOpened & "#;"
CurrentDb.Execute strSQL, dbFailOnError
View 9 Replies
View Related
Jun 14, 2005
I am trying to use the double click event of a listbox to open a form to a specific record. The problem I have is when I double click a record, Access pops up a box asking for PONumber (which is in column 0 of the listbox).
Here is the code that I am using:
Private Sub lstPOInfo_DblClick(Cancel As Integer)
Dim boStatus As Boolean
Dim strDocName As String
Dim strLinkCriteria As String
strDocName = "Orders"
' function checks to see if the form is open or not
boStatus = fIsLoaded(strDocName)
If boStatus = True Then
strLinkCriteria = "[PONumber]=" & Me.lstPOInfo.Column(0)
Forms![Orders].FilterOn = True
Forms![Orders].Filter = strLinkCriteria
Else
strLinkCriteria = "[PONumber]=" & Me.lstPOInfo.Column(0)
DoCmd.OpenForm strDocName, , , strLinkCriteria
End If
End Sub
I'm sure it's a syntax error on the "strLinkCriteria = "[PONumber]=" & Me.lstPOInfo.Column(0)" line.
View 5 Replies
View Related
Oct 6, 2004
Hi All,
I have a form called "frmProducts" which is linking to table "tblproducts".
Easy Enough...
The form shows all the Products information (fields).
I have a "More Info" command button (cmdMoreInfo) next to every record in the form.
When the command button is clicked I would like it to open up another form "frmMoreInfo".
But On frmMoreInfo I would only like it to show that specific product.
I have tried the following code on the on click event on the command button but it was always asking me for the value of Form![frmMoreInfo]![txtInternalCode]
Code:Private Sub Command12_Click()On Error GoTo Err_Command12_ClickDim stDocName As StringDim stLinkCriteria As StringstDocName = "frmMoreInfo" stLinkCriteria = "Form![frmMoreInfo]![txtInternalCode]=" & Me![txtInternalCode]DoCmd.OpenForm stDocName, , , stLinkCriteriaExit_Command12_Click:Exit SubErr_Command12_Click:MsgBox Err.DescriptionResume Exit_Command12_Click End Sub
Any Ideas?
View 4 Replies
View Related
Oct 22, 2014
I want to ask what is the code for Macro Builder in a command button if I want to open a report from the given value of inputbox. I made a command button to open a report but it will ask first the required value from inputbox I put. then it will open the report of specific record from the value I input in the inputbox.
View 3 Replies
View Related
Dec 8, 2014
I'm very new to access database and I'm trying to create a macro that allows the user to enter data after seeing a mistake in a form. I need a macro that will open the specific table and record of a piece of data. For example there is a student TestName3 whose grade on a competency is 30% but should really be 45%. I want a macro by the competency percentage to allow the teacher to edit that without looking through the entire table. The macro should prompt first to ask if the teacher is sure they would like to edit, second prompt asking for the student's unique ID number, and finally be taken to the specific record and table related to that competency.
I have tried to use a vastly overcomplicated DLookup and Order column to give me the value for a acGoTo search.
Here's what I have.
Option Compare Database
Dim answer As String
Dim response As Object
Dim gotoresponse As Object
Private Sub Command71_Click()
On Error GoTo Command71_Click_Err
[Code] ......
View 5 Replies
View Related
Dec 17, 2013
In my Access adp, when I open a certain form (frmVertebrates), the databinding occurs in the Load event for the form, for various reasons. I have not specified any datasource in the form's design view.
Databinding for frmVertebrates:
Code:
Me.Form.RecordSource = "select * from dbo.vertebrates where catalogID=3"
This works great when just opening the form. However, when opening with a filter from a button on another form it won't work, the form displays all records.
Button code:
DoCmd.OpenForm frmVertebrates, , , "vertID=123"
Obviously, this makes sense, since the record source is explicitly set in the Load event.
Is there a way to capture the filter "vertID=123" so it can be added to the Form.RecordSource sql?
When breaking in the Load-code and watching the 'form' variable, I can't spot the filter condition anywhere...
View 4 Replies
View Related
