Field With Data From A Different Query Than RecordSource
Aug 31, 2005
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?
View Replies
ADVERTISEMENT
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
Apr 10, 2006
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
View 2 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
Oct 11, 2006
Hi
I have some drop downs on my form, which has some code that decides which recordsource to use.
When I first select an option from the drop down, the query takes 10 seconds to load data.
I then select another option, which runs a different query, which takes x seconds.
If I then select the original option from the dropdown, the data loads onto the form in less than a second.
Why is there a delay the first time round and not the next? Has Access saved some kind of query plan? If so, why doesn't it save it permanently?
View 1 Replies
View Related
Aug 18, 2006
How do i make my subfrm record source switch to another saved query (qryPendingStatus) when it's opened from the form.
Heres what i am trying to accomplish:
I have a subfrm that i'd like to use to display info in 2 different forms.
There's a form currently for All statuses. Then another new one for Pending status. I'd like to use the same subform for this pending status form also, and in doing that my qryAllStatuses, but change to qryPendingStatus on the subfrm when i open the mainfrom.
This is on the open even ofthe mainform, but does not work (says method not founds...so obviouly i can't use ".Recordsource" here:
Private Sub Form_Open(Cancel As Integer)
Me.subfrmJobInfo.RecordSource = "qryPendingStatus"
End Sub
How can i make this happen?
View 3 Replies
View Related
Jun 28, 2013
I have two tables that are joined - called A and B. A has two fields, "PLACE" and "RAND" and is joined to B via field "RAND". Other than "RAND", B has several fields named 01 TOTAL, 02 TOTAL, 03 TOTAL, etc...for about 60 fields.
The "PLACE" field in table A has data that is 01, 02, 03, ect.... How do I structure a query so that whatever "PLACE" is, I can match the XX TOTAL value? In other words, i need to have the query field header be somehow dependent on the value in "PLACE".
Is this even possible?
View 4 Replies
View Related
May 18, 2006
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
View 2 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
May 27, 2005
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."
View 2 Replies
View Related
May 30, 2005
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!
View 3 Replies
View Related
Sep 26, 2005
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.
View 2 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
Oct 31, 2006
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.
View 1 Replies
View Related
Mar 2, 2005
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!
View 3 Replies
View Related
Sep 4, 2006
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 ?
View 1 Replies
View Related
Sep 26, 2005
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.
View 7 Replies
View Related
Jan 5, 2008
Hi all, firstly please forgive me if i'm a little slow, i haven't done any access work for a year or so and i'm a bit rusty :o
Basically i have a customer database which holds customer details and orders. It's been created and working fine for a couple of years, but i recently found something that i'm having trouble with. tblCustomers holds most of the data and links to other tables such as tblCustomerPostCode/tblCustomerEmail with the primay key CustomerNumber. I put certain data such as these in other tables because i was taught to not include fields with lots of blanks and these wouldn't necessarily always have data. The relationships table is attached - relationships.jpg
I also have a main table for customer orders - tblCustomerPurchases which holds data such as date of purchase and purchase cost. This has a primary key field called PurchaseNumber, and links to tblCustomers with a CustomerNumber field. This meant i could have many purchases for one customer, etc.
Everything has worked great as i said and i have many forms, reports etc with no problems, and the problem i have hasn't shown itself until now.
If i have a query with tblCustomers and all the relevant other tables linked to it, all fields work perfectly and i can add, edit etc whatever data i need to. See qselcustdetails.jpg attached.
The moment i add the tblCustomerPurchases or anything that uses it to the query however, i can no longer add data to some of the fields such as tblCustomerPostcode, tblCustomerArea, tblCustomerEmail etc. If there is data in the field already then i can change it, but not add new data. See qselcustdet&purchase.jpg attached.
I believe it must be to do with the fact that tblCustomerPurchases uses the CustomerNumber field as well? And it then doesn't allow tables such as tblCustomerPostcode to create new data? But i can't understand why. Like i said i'm a little rusty and i can't figure it out at the mo :confused::rolleyes:
Also, can anyone tell me any suggestions for the easiest way of fixing it? The database already has 5000 customers and plenty of forms, reports etc.
Many thanks in advance :D
View 1 Replies
View Related
Oct 3, 2004
Hi,
Am currently working on a project for AS IT. one problem that i need to come across is trying to see data in a field that comes from a query?!?! Confused?! so am i!
When i first open the form, a paremeter query appears asking me to insert a surname (which i have created) when i insert the surname....and click on the drop down button, it gives me three values...ForeName, Surname and class
When clicking on the pupils name, i want the Class of the student to be inserted into the Class Field!
Can anybody help?
Danbwest
View 1 Replies
View Related
Apr 18, 2005
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!
View 8 Replies
View Related
Jun 29, 2005
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
View 3 Replies
View Related
Nov 4, 2005
I have a form with two subforms. One of the subforms has five subforms (subsubforms).
The ControlSource and the (query sequence for each) RecordSource of each of the subsubforms are created on-the-fly. The subsubforms have no Master/Child relationships specified. The subsubform merely display various filtered and various grouped subtotals of the 1st subform. Summing and grouping on the subsubforms prevents my using Master/Child properties.
On the AfterUpdate event on any of the filter fields on the form the ControlSources and RecordSources are rebuilt and the form becomes invisible for some reason and appears to hang, even after stepping through all the (class module) code with the debugger. Repeadedly clicking both Maximize and Restore buttons on the form's button on the Status Bar followed by clicking a 2nd form's toolbar icon causes the form to become visible under the 2nd form. The tool bar are also not visible, just a large brow bar at the top of the form. Moving the mouse over the toolbars causes individual icons to become visible or raised.
What am I missing?
Maybe I should calculate the values using DAO recordsets and backfill unbound subsubforms.
Thank you in advance for your thoughts.
View 1 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
Jul 25, 2006
Hello,
I have a dropdown menu which has a lisrt of dates (mmm yyyy), and when one is selected it updates 2 boxes with 01/mm/yyyy and 30 or 31/mmm/yyyy
Private Sub cmbDate_AfterUpdate()
Me.frmData.Form.RecordSource = "SELECT * FROM qry_NatxReg WHERE [Company Name]='" & cmbCompanyName & "' AND [Time of Attempt 1] BETWEEN #" & txtDateFrom & "# AND #" & txtDateTo & "#"
Me.frmData.Form.Requery
End Sub
When i choose June, it works fine and gets all the june dates from [Time Attempt 1]
But when i Choose July, it includes June.
I only have data for June and July as it's still in testing.
I can't wrap my head round it!!
View 12 Replies
View Related
Sep 27, 2006
I have a form that is based on a query. I would like to change the form's RecordSource to another query using SetValue in a Macro.
Under Action Arguments I have
Item: Forms![Receiving Query subform].RecordSource
But I don't know what to put for an expression. I would like to use the same form but base the RecordSource on a query called "Receiving Search by Date". :eek:
Steve
View 4 Replies
View Related
