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 RepliesI 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] & "*"));
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?
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 RelatedI 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.
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.
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
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.
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.
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] ...
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?
I have a combo box (cboManifestNumber) that is based on the following table:
tblManifestData
ManifestDataIDPK (autonumber PK)
ManifestNumber
RemovedDate
ManifestComments
TsdfIDFK (FK frm tblTSDF)
This table is related to:
tblTSDF
TsdfIDPK (autonumber PK)
I need to be able to update tblManifestData with a new manifest number and manifest comments, along with assigning it a TSDF. how to be able to enter a new manifest number and the associated data without having it create two lines in tblManifestData. I thought that I could enter a new manifest number, then requery the table and form so it shows the complete list of manifest numbers (including the recently entered one) while staying on the newest entry.
Using this code as part of a module to open a report in print preview and set it's recordsource to the sql I have written earlier in the code. The problem is when I run this it only prints the report it won't show it to me in print preview like I want. Anybody know what's wrong? Thanks...
DoCmd.OpenReport "SitesIssues_qry", acViewNormal, "", "", acNormal
Me.RecordSource = strsql & " " & strWhere & "" & strOrder
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.
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
How do I determine what value my listbox has?
Here is what generates the records from Form 2:
If Not IsNull(Me.[StartDate]) And Not IsNull(Me.[EndDate]) Then
strCriteria = strCriteria & "tblWO.Created Between #" & _
Format(Me.[StartDate], "m/d/yyyy") & "# And #" & _
Format(Me.[EndDate], "m/d/yyyy") & "#"
strSql = "SELECT DISTINCT [WOnumber],[Requested],[UDF1],[RequestedBy]," & _
"[Status],[property],[asset]" & _
"FROM tblWO " & strCriteria
Forms("frmFilter").Listbox1.RowSource = strSql
What I am trying to do is trigger a message box from the onclick of a command button on Form 2 and read the listbox # records on Form 1. It's gotta be something simple in my syntax. Search hasnt provided this scenario for me. I have tried these and more:
'If Forms("frmFilter").RecordsetClone.RecordCount = 0 Then
'If IsNull(Forms("frmFilter")).Listbox1 Then
'If Forms("frmFilter").Listbox1.RowSource = 0 Then
'If Forms("frmFilter").Listbox1.RowSource = Null Then
'If [Forms]![FrmFilter]![Listbox1.recordcount] = 0 Then
'If Forms("frmFilter").Listbox1.RecordCount = 0 Then
'If Forms("frmFilter").Listbox1.RecordCount = "" Then
'If Forms("frmFilter").Listbox1.RecordCount = Null Then
MsgBox "Sorry, no records meet your chosen dates. Change the dates and try again."
Hi everybody,
I have a form with its tabbed subforms' recordsource determined by the code:
Private Sub childTvl_Enter()
Me.childTvl.Form.RecordSource = "SELECT * FROM tblTvlInput WHERE tblTvlInput.Dept = """ & Forms.frmBudget.txtDept & """"
End Sub
This is to filter the huge data by department and only allowing the specified department to view their code. However, I find it slow and pesky as it only shows the required information when the user clicks on the child. I have tried putting the code above elsewhere but to no avail.
Is there a better way of speeding the filter as well as showing the user his own records when he clicks on the tab?
Thanks in advance. =)
You guys here are saviours!
I am trying to correctly code a Record Source value in a Form's 'On Open' Event . I first captured parm data from the calling Form, (Last Name, and First Name) to be used in the query as follows:
Private Sub Form_Open(Cancel As Integer)
Dim intI As Integer
intI = InStr(Me.OpenArgs, ";")
SearchFirstName = Left(Me.OpenArgs, intI - 1)
SearchLastName = Mid(Me.OpenArgs, intI + 1)
Me.RecordSource = "SELECT tblName.LastName, tblName.FirstName WHERE tblName.LastName = SearchLastName and tblName.FirstName = SearchFirstName"
End Sub
If I leave off the 'WHERE' clause, I get all the records in the table, so I am sure the syntax in that area is all wrong.
Today my client asked me to do something strange.
He wanted a field to be in the subform which was originally part of the parent form and is still part of the parent table.
(Hence, when I change the field in one subform line it would change for all other subform lines related to the current parent record) He insists to put this in the subform and it should be Editable there.
I accomplished this by basically putting a 'Parent table' join 'Child table' query in the subform so I could have access to this one field. Orginially I just had a query with the Child Table but to get access to that one field (and to make it editable) I made this extra join.
I don't like this design eventhough it works..... Any suggestions on how to effectively implement this.
Also I don't understand how Access still knows what to do using the Parent Link And Child Link fields (Located in the Subform Control). How does Access know to create a new record in the Subtable and not in the Parent Table when both are present in my subform's record source now.
Hello,
I have a combo box that i would like to dynamically change the recordsource (what values are available in the dropdown) based off of the criteria in another field. Basically, I want to switch which query the combo points to.
I am looking to do something like...
If field1 = nulll
combo recordsource = query1 (a list of values from table1)
Else
combo recordsource = quer2 (a list of values from table2)
I am looking to switch which query the combo is populated with, not limit the rows based on field1.
Any Ideas for this? Thanks!
I have a form with 5 tabs. Each tab has 2 subforms.
When the form loads I am trying to select it's recordsource by doing this:
Me!Subformname_A.RecordSource = "SELECT FieldA, FieldB FROM TableA WHERE"
However, it throws an error.
Is there something wrong with this ?
I have a report that can be used for 3 different queries. I've been trying for the last hour or so to get three different buttons on a form to open this report but give it an appropriate record source.
I figured there's got to be a way that's easier than three queries, and three reports...?
I was trying to use the same format as with forms :
[Reports]![rptReportName].recordsource = "qryQueryName"
or variations of that. Am I way off? It keeps saying that I've misspelled the name or it doesn't exist.
Hello,
I'm trying to create navigation buttons that navigate in alphabetical order. I know that the easiest way to do this is by creating a query and using the query as the recordsource for my navigation buttons, but for the life of me I cannot figure out how to actually change to the recordsource to that query for only that navigation button.
I'd appreciate any help
Thank you
PRHC
Weird yet aggravating problem.
I am trying to setup a routine that will allow the user to change the RecordSource of a subform. The RecordSource is being changed yet once I move to another record the subform is reverting back to the original subform RecordSource. I am using the below command to change the subforms RecordSource...
Me.MySubformName.Form.RecordSource = MySQL
I even to make a copy of the subform and I changed the table it is linked to yet I got the same result
Me.MySubformName.SourceObject = "MyOtherSubForm"
There are no functions in the record selectors that would interfere with the subforms RecordSource.
Any suggestions as to why the RecordSource of my subform will not "stick" when I move to another record?
Thanks in advance for your help!
Hi
I have problems working with a 3 level form
I have one main form called Projects
Depending on the record on the form Projects, the subform called Products will display the correct info
And on the OnCurrent event of the form Products, it will display the correct info for another subform called Product_Info
The problem is, the subform called Product_Info is dependant on a field on the Projects form, and on a field on the Products form so I cannot directly link the Product_Info form with either the Projects of Products form
What I'm doing is changing the recordsource of the Product_Info subform on the OnCurrent event of the Products subform but for some reason it won't work
If I display the recordsource of the Product_Info subform, it displays the right recordsource triggered on the OnCurrent event of the Products subform, but the data doesn't display
Thanks
I have a form that pulls data from a big joined RecordSource query. This form needs to be editable. I need to add a field on the form that will contain a value, which is actually the sum of numbers from several records. Because that sum value includes arithmetic in the query itself, I'm assuming that is why the results of the query are not editable. I tried joining the Query that gets that sum value into the main RecordSource statement, but because the sum Query is not editable, doing that renders the whole form uneditable.
I am new to working with Access forms, and VBA in general. I have a lot of experience using VBscript, though. I was asked to get this done and now I have to fight my way through it. I'm hoping there is maybe a way to write a function that will query for the sum value based on the current ID on the Form. The main form cycles through records, and the sum value is a total of points for the record shown in the form. So as each record loads into the form, I need to get the points total that matches that record's ID and display it. The points total does not need to be editable.
Any suggestions for a newbie on how to solve this?