Forms :: Dynamically Setting Recordsource For A Form
Mar 25, 2014
I currently have a Job Management System where the Customer Order Form displays all jobs via a main form and an order details subform. This form is used for inputting new jobs and for viewing existing jobs via links from other reports such as a job schedule or customer orders report. It is always reached via a clicking of a "New Customer Order" Button where the form is filtered to new record or via double clicking on an order number in another screen and filtered via the order number.
I am now in a position where the database is growing too large and I want to archive the data in the Orders / OrderDetails Tables into Financial Years and put them in a separate backend database. However the jobs will still need to be viewed via the customer order form in order for them to be duplicated the following year.
I have created a union query of the PreviousFY and Current Orders to allow the "search jobs by customer" query to work. This works fine, but I now need to make the record source of the Customer Order form dynamic.
I have created a CurrentFY query to feed the form if it is a current job and a PrevFY query if it's from the previous FY.
I have written code to check the OrderDate against the financial year to work out which database the job is from and set recordsource according to that result. This is currently in the OnCurrent event as I also have other code there that checks details of the order to determine if fields should be editable.
However I am finding that when I parse the order data between the forms the "new record" command or the order number is not being retained or checked. If this because I don't have a recordsource set when it opens?
I tried setting the recordsource as the currentFY on open and then do the check but that didn't work.
View Replies
ADVERTISEMENT
Oct 18, 2014
I am trying to add a feature in my database dashboard that shows a list of hyperlinks that are the most frequently used forms and reports based upon the user. I can figure out how to capture the event each time the form or report is opened and update a record to increase the count, and build a recordset that will query the table filtering by user and order by frequency...
However, I am stuck on building the hyperlinks that will point to the correct form or reports dynamically.
Specifically, I am not able to update the subaddress using VBA. I get an error that the hyperlink cannot follow to the new form...
Here is my code:
Code:
Private Sub Form_Load()
Dim strUser As String
Dim strApp As String
strUser = Environ("username")
strApp = "frmTest1"
Me.txtUser = strUser
Me.txtLink3 = strApp
Me.txtLink1 = strApp
Me.hypTest.Hyperlink.SubAddress = strApp
End Sub
View 3 Replies
View Related
Jan 26, 2005
This is driving me mad. I'm trying to copy a table and form from a template.
They both copy fine, but when I try to change the new forms recordsource property, nothing happens.
Private Sub CreateQuiz_Click()
'copy structure from existing BLANK table
a = InputBox("Name of new table", "Create new Quiz")
DoCmd.CopyObject , a, acTable, "BLANK"
DoCmd.OpenForm ("BLANK")
Forms!BLANK.RecordSource = a
Forms!BLANK.Close
DoCmd.CopyObject , a, acForm, "BLANK"
Forms!a.RecordSource = a
End Sub
Any help would be much appreciated.
View 1 Replies
View Related
Jan 27, 2005
Hi All
I have a Main Form, This Main Form has a Subform, the SourceObject for the subform is another form.
What I am trying to do is to set the RecordSource for the SourceObject Form from within the code behind the main form so i can use the same form with various queries.
Appreciate any help - thanks
View 2 Replies
View Related
Apr 23, 2015
I am currently working on a Database to automatize some process.
The User can import a CSV (Text) File via a DialogBox, which gets imported into a Table. After this, the content has to be filtered, setting conditions on 4 different rows. The new Table gets exported into a new CSV (It has to be CSV, since it later gets imported into SAP)
How can I set a Filter on a dynamically created Table (using VBA)?
Best case would be to save the filter into my Import spec, but Access doesn't seem to have this option (?)
View 3 Replies
View Related
Apr 5, 2014
I have a query that I am using as recordsource for a form. I also have a list box that I am using to change records on the form.
My issue is I need to use the same form for similar queries. For example, I want to open the form with only "Manager1" records displayed. So I need to do a different query. I don't want to use parameters or have employees type the manager in. It needs to be just a button they click.
So my two options are - make 10 copies of the form each with their own new query recordsource for each manager, or use one form and dynamically input the recordsource before the form opens..
I want to do that -- but when I try the list box gets all screwed up. Because the list box is relying on another SELECT query to populate. I have 10 buttons each with a different recordsource query feeding into the form when I click but I get so many errors in the fields when the form opens.
View 3 Replies
View Related
Mar 9, 2013
I've got a DB, which has 3 forms ( all 'under' 3 navigation buttons/tabs ). The main form ( Client's form ) is set to show first, and currently has a recordsource of about 1350 records. I think the size of the recordsource, and the fact it 'loads' the other two forms all 'in one go', is slowing it down ( between 5 and 10 secs. to load ).
I read I should set the Visible properties of forms, not shown immediately, to false, only setting them to True in the On_Load event - when Tab is selected. Ideal I thought. However, I can't find this property in the Form's property sheet.
View 1 Replies
View Related
Feb 16, 2014
I have a simple add form that will add an entry to one of two tables: Box 1-1, or Box 1-2.
They have the exact same fields. In the add form, I want the user to specify a Rack field and a Box field. Box 1-1 would be Rack 1, Box 1. Box 1-2 would be Rack 1, Box 2 (there will be more tables later, but just using two for now until I get it working).
But right now my form always adds to the table Box 1-1 - I can't get it to switch.
I've added the code I have so far below. The first part is what I'm having trouble with, the second part just uses a Submit button to add a new record - I'm just including it in case it's interfering in some way I'm not seeing. I'm running Access 2007.
Code:
Private Sub Switch_BeforeUpdate()
If Me.Rack.Value = "1" And Me.Box.Value = "1" Then
Form_Add.RecordSource = "Box 1-1"
ElseIf Me.Rack.Value = "1" And Me.Box.Value = "2" Then
Form_Add.RecordSource = "Box 1-2"
[Code] ...
View 7 Replies
View Related
Nov 12, 2013
I have a form with a (large) number of controls which acts as a kind of read-only dashboard of data. The data is split across a number of tables (4, for now, but may grow) I've created a query which amalgamates all of the data from the tables into a single dataset and set that as the RecordSource (Snapshot) of the form. I now want to bind each of the controls to a particular field returned by that query.
However - I have a combobox (same form) which allows users to select a date and I need the controls to update with the relevant data for that date whenever the combobox is changed. The query already returns values for each field over a range of dates so the data is available within the form's RecordSource - I just don't know how to include the date criteria, as specified by the combobox, when binding each individual control?
View 2 Replies
View Related
May 24, 2013
I would like to add a page to a tab form when a certain button is clicked. I don't want to use the visible/hide solution. So I already found out that this can only be done in design view. I am using the following code:
Code:
Private Sub cmd_StrategyAdd_Click()
DoCmd.OpenForm "Form1", acDesign
Me.tabMain.Pages.Add
DoCmd.OpenForm "Form1", acNormal
End Sub
I am getting the error: Run-time error 2467. The expression you entered refers to an object that is closed or doesn't exist.I am sure the name of my tab form is tabMain.
View 14 Replies
View Related
Apr 27, 2013
Is it possible to assign a query to the recordsource of a form dynamically? I assume the answer is yes. Can this action be done when the form is not open? If yes what is the correct syntax to use when the code is in a normal module as a public sub? I tried many possibilities and could not get it to work.
Code:
Dim ForNm as string
Dim Qry as string
ForNm = "PersonalFm"
Forms(ForNm).Recordsource = Qry
Forms![PersonalFm].Recordsource = Qry
[Forms]![PersonalFm].Form.Recordsource = Qry
View 14 Replies
View Related
Apr 16, 2014
I have a single form which becomes either readonly or editable or addnew using a circular toggle switch. The toggle switch is activated by a command button and the caption of the command button is used as the display of the mode.
The SetAddModeBt is a separate command button that initiates the add mode. The problem is that various form or field properties when defined on the fly are not stored and they get lost when you open the form again. Look at the code below:
Code:
Sub tglEditBt_Click()
'-------------------------------------------------------------------------------
' Circular toggle button to change display mode of the form
' ReadOnly - Edit - Add
'-------------------------------------------------------------------------------
' On Error GoTo NotFound
Dim ForName As String
Dim strSQL As String
Dim FMode As String
[Code] ....
Here the first two cases fail when executing the last statement saying it is not supported.
View 5 Replies
View Related
Nov 24, 2005
Is there a way to set the recordsource for a form before it opens? I've got Me.recordsource = "QryNewCaseForm" on the On Open event of the form but I toggle between recordsources once the form is open and if I close it while the recordsource is "QryCaseForm" it won't open as "qryNewCaseForm" the next time I open it.
View 2 Replies
View Related
Jun 24, 2013
When I right click a row on the data sheet side of a split form an select "New Record" I want the curser to go to the first field on the single record side. I've placed this in the OnCurrent but it did no good.
Code:
If Me.NewRecord Then
Me!Descrfiption.SetFocus.
End If
Any way to set the focus to the single form Side of a split form?
View 4 Replies
View Related
Jul 10, 2014
I have a Main Form with a tab control. On each of the six tabs is a SubForm that takes up the entire tab. So basically I have a tabbed interface for my forms, just with a bit more control over the layout than if I just opened the forms separately in the Access window.
Anyway, on one of those subforms is a button. When you click it, it opens a separate form in a pop-up window, which is used to find a specific record. Once that record is found, you can click another button, and the idea is that this pop-up window closes, and you return to the underlying form, which jumps to the record you selected.
So far so good. Now, if I am working on my program in Access, and I open that subform's source form separately in its own tab, click the icon to open the record finding form, find a record, and then click the button to load that record, the following code runs, and it runs flawlessly - closing the pop-up window, and passing the information back to the main form which displays the proper record:
Quote:
Private Sub ViewRecord_Click()
Dim RecordID As Integer
RecordID = Me.ID
DoCmd.OpenForm "CDLExam", , , "ID = " & RecordID
DoCmd.Close acForm, "CDLExamCONT", acSaveNo
End Sub
Where my program falls apart is that, in actual use, this form is not open on its own. It's open as a subform on one tab of a tab control on a Main Form. So the third line of code falls apart. Access thinks I want to open CDLExam separately, but it can't because it's already open in the subform, so instead I just end up back at the main window like I want, but the form fails to move to the proper record. Basically, line 3 just doesn't do anything.
How can I make this work? I tried replacing "CDLExam" with the name of the main window, but then it tries to move to the record in the main window, which throws an error as the main window doesn't even have a record source attached to it.
how to refer to the SUBFORM which has CDLExam open, and tell THAT to move to the proper record.
View 14 Replies
View Related
Jan 7, 2014
I have a recordclone combo box for navigating between 240 company records, so the form will go to the correct record on updating the combo. Instead of clicking on the down button and scrolling through the list, I would like to enter letters into the box and update the record source automatically as I enter them.
I'm not sure what events to use since there appears to be very subtle differences between them (eg between 'on dirty', 'on change' and 'before update').So when I type in 'T', only those companies that start with 'T' are viewed in the combo box.I have discovered the VBA 'dropdown' command which would be useful for the user.
Code:
company.dropdown
Table_Company
Company_ID: autonumber
Company_name: text
The name of the form is "form1" and the combo box name is "company".The record source for the combo box is "query_company". In the form the ID field is hidden and the company name is not hidden.The criteria I have for the combo would be something like this.
Code:
SELECT Table_Company.Company_ID, Table_Company.Company_name
FROM Table_Company
WHERE (((Table_Company.Company_name) Like [forms!]![form1]![company] & "*"));
View 3 Replies
View Related
Mar 13, 2014
I know this is probably already in the forum but it is getting pretty big and I can't find the answer. I have this:
frmMain frmMainSub frmMainSubSub recordsource = query1
How do I change query1 to query2?
View 2 Replies
View Related
Jan 9, 2014
Is there a way to setup the format on fields to only allow text inputs, no numbers wanted. I know I can do this at the table level but is some cases numbers are ok. So I want to code it so I can control when numbers are ok and when there not.
View 1 Replies
View Related
Jun 5, 2013
I have an Access 2003 database. I have a form with 5 tabs. I want to prevent some users from editing data on some of the tabs.
I added some logic to the OPEN property of the parent form but it does not work. "Object doesn't support this property or method." What is the correct way of controlling access of the data on the tabs?
In the logic below I first determine the user's permission for each tab. If the user has read-only access they will not be allowed to edit anything on this tab. There are 50 fields on the accounting tab and a subform. If possible, I don't want to have to specify each field individually in the code. Plus there are 5 tabs with numerous fields on each.
BTW: [add designer] is the parent form and [accounting] is the tab.
If DLookup("[permission_studentacct_ReadOnly]", "Users", "Contact_ID = " & Forms![Global]![UserID]) = True Then
Forms![Add Designer].[Accounting].AllowEdits = False
Else
End If
View 3 Replies
View Related
Sep 2, 2013
I'm using Access 2007. I've created a table with two fields. "Novice and Recertification" as in combo box.
I put it on the form. Now the idea is when a user clicks Recertification, it shows up on the report. When the user clicks on Novice, it should be empty on report (Reason why I want to keep novice is so that we have a record of it.)
Now the challenge is I added another column, empty field for Novice and Recertification for Recertification. Thing is if I set the bound column, I select on an empty field on form and it will be empty on report. But I want the user to see Novice on the form and it must not show on the report.
View 3 Replies
View Related
Nov 5, 2014
so I have a commandbutton on a form, when I click on the button I would like an inputbox that lists the subforms and subforms recordsource/ controlsource.
View 4 Replies
View Related
Mar 14, 2013
I am assigning a recordset using vbs code. The variable "CB_proj" is a combobox value. The problem comes when the combobox is null, the program errors out and even if I try to exit the routine with an if statement, it still compiles the line of code and errors out because the object doesn't exist. how I can get it to not compile that error if my combo box value is null?
CB_proj = Forms!CC_MAIN.CB_proj
strSQL = "SELECT " & [CB_proj] & ".* FROM " & [CB_proj] & ";"
Forms!CC_MAIN.projdat.Form.RecordSource = strSQL
Forms!CC_MAIN.projdat.Form.Requery
Forms!CC_MAIN.Form.Requery
View 3 Replies
View Related
Dec 18, 2013
I didn't have this problem when I had used subforms, but there were little bits of thins I had to polish. There are basically 2 tables, and the form is based on the table PKing. It shows the PK and a few irrelevant fields. However, when the recordsource now inner joins the two tables based on the PK, it only turns up 1 record.There are no filters, and nothing is spelled wrong. I checked all of the control sources, and nothing's off. What am I doing wrong?
The one record that is shows is perfectly fine.
View 7 Replies
View Related
Aug 23, 2013
So I have two forms: Af and Bf
In Af, when a button, Abtn, clicked, it'll run
Code:
Me.Visible = False
DoCmd.OpenForm "Bf", , , , acFormEdit
Forms![Bf].Form!Blabel.Caption = Me.Atxtbox
Forms![Bf].Form!Bcombox = Me.Atxtbox
Then Af lost from view and Bf appears in form view, with Blabel and Bcombox show the value of Atxtbox.
BUT if I try something like this in Bf when an event happens
Code:
Me.Bcombox2.RowSource = "SELECT xx FROM tablexx WHERE yy = '" & Nz(Me.Bcombox) & "'"
Nothing will be shown in Bcombox2, because Me.Bcombox return Null. I know this from doing
Code:
MsgBox Me.Bcombox
and the message box will shows nothing. The same thing also happen with the Blabel's caption. In form view, it shows Atxtbox value, but when I accessed its caption's value, it returns Null.
What I wanna do is:
1) Open Af, insert value to Atxtbox
2) clicked Abtn, pass the value of Atxtbox to Bcombox
3) Af is closed, and Bf is opened
Why the null...?
View 5 Replies
View Related
Mar 4, 2014
I have a tabbed form. The main form is titles ContractDtlsFRM. There are 3 other subforms in separate tabs. The first field in the ContractDtlsFRM is Contract No. I would like the form to sort in ascending order by this number.
I tried entering the following code in the Forms Order By event but it didn't work
Private Sub Form_Open(Cancel As Integer)
Me.OrderByOn = True
Me.OrderBy = [Contract No]
End Sub
View 2 Replies
View Related
Jan 15, 2006
Hi,
I want to write Visual Basic language in the form with one button:
I have one form named "setForm" with one button and one text box:
Let's set the new table "frm2", the new form "frm2"
Dim sql As String
sql = "SELECT [model].* INTO frm2 FROM [model]"
CurrentDb.Execute sql
DoCmd.CopyObject CurrentDb.Name, "frm2", acForm, "frm1"
Forms!frm2.RecordSource = "frm2"
The last statement code doesn't work: It is said that Microsoft Access cannot find the form "frm2" referred to in a macro expression or visual basic code.
How can I change the recordsource? Thanks.
View 1 Replies
View Related
