A subform has 4 fields, State, Location ID, Total and District.For the first 3 fields ( State, LocationID, Total ) the data is obtained using a query 1. This is set up in the design mode..For the last field, District, data needs to be obtained by passing locationID to
Table 2.
E.g. select district from table 2 where ID = @locationID
How can I write a query where locationID is passed as a parameter in the design mode.
I want to filter my subform data, to only show records where field A is a higher value than field B.
Code:
Me.MySubform.Form.Filter = "A > B"
Me.MySubform.Form.FilterOn = True
This way it doesn't find field B.
Code:
Me.MySubform.Form.Filter = "A > " & MySubform.Form!B
Me.MySubform.Form.FilterOn = True
This way it seems to filter all record to the field B value of the first record.
I have an access 2010 database with an initial form for user login. After selecting their login, users go to the main tabbed navigation form. On the first tab, I have form with a dashboard as a subform containing a field that says "Welcome" with the user's name (obtained from the login). My problem is, users have to click on the subform for the name field to update. How to have the name automatically populate when the login form closes.
View 2 Replies View RelatedI have a main form and 2 subform. The first subform has a field for %. And the 2nd subform has series of checkbox (Checklist or error made by the student)
Because every checkbox will lower the mark from 100 to x points per checkbox.So, I want that, if the field is not set to 100 yet, then they will not be able to check any box in the subform.I tried, before update of the subform, etc, but none work properly.
I am trying to create a simple budget planner. So far, I have the following table:
tbl_account [id|acc_type|acc_name|balance|memo]
tbl_expense [id|exp_date|exp_amt|exp_acct|exp_payee|exp_type]
tbl_expense_cat [id|cat_name|cat_type]
tbl_income [id|inc_date|inc_amt|inc_acct|inc_payee|inc_type]
tbl_income_cat [id|cat_name|cat_type]
The relation now are:
[tbl_account:id] ---> [tbl_income:exp_acct] --> [tbl_income:id]
|_______> [tbl_expense:inc_acct] --> [tbl_income:id]
I have 3 subform on the main form:
[account_sf]
[expense_sf]
[income_sf]
I have set all them to continuous. Now I have added 3 account in the tbl_account.
I wonder how to SUM all the income of certain account, and minus all expense of certain account, and have them showed in the subform tbl_account.
Here is the attach pics: Untitled.jpg
How do I reference a subform DS field to open up a tabbed form on another subform.
Here's what I have.
field one on the Sub DS opens up tab one on another form, as does field two on DS open tab two on another form.
but..
how can I click on "any" field on the DS, run an IF statement, and it checks if that record on the Datasheet, contains information in regards to, IF field one OR field two contains any data, and opens the tabbed form respectively on the other form?
I have a form containing client demographics. One of the fields is the DateOfBirth. I would like to control the visibility of a subform based on this DateOfBirth field.
I have tried using the code below:
Private Sub Form_Current()
If DateOfBirth > 11 / 11 / 1994 Then subEligibility.Visible = False
End Sub
This works when the form is opened for the first record, but the visibility does not change as I scroll through the rest of the records.
I have a subform that I use in a few different ways on my database. It has a comment log with a memo field for people to insert their comments. The struggle is some can be long some can be short. Is there any way to have each record height adjust to the length of the memo?
If it cannot be done for the subform in regular use, my main concern is having it formatted for reports that are generated for clients.
I clipped and attached an example from a report. Its not the best example but the extra column height is adding additional pages to my report to accommodate one long comment.
How to add attachments to a email using a sub form were I store the attachment paths
E.g I have a main form called frmteaminfomer with various fields and continuous subform called attachmentssubform in the sub form I have a field call txtaddress . I have some code that I can pick a file then put its file path into txtaddress field and also have a check box call add to email. so what I trying to do is loop through all the records in the subform and if the check box is true add the files as attachment on the email.
I have a code that works great from the parent form but I decided to change the format and call it from a lostfocus event in the subform instead. Now I keep getting error 3314:"You must enter a value in the tbGuests.LastName field".
The code should copy the parent form fields and create x number of duplicate records according to a field on sub-form. It then runs an append query to add the information from the subform.
Code:
Private Sub GuestsInParty_LostFocus()
Dim partymsg As Integer
Dim dbs As dao.Database, rst As dao.Recordset
Dim F As Form
Dim intHowMany As Integer
Dim intCounter As Integer
[code]....
I have a form and a subform
Which method should I use if I need to update automaticaly a field in form based on an event in subform ?
Just to be more clear, have a look in the below example.
In first print screen, student "Johnson Nick" has not completed all tests in TestA subform, the form field "DateCompleteA" is blanc.
Once a student completes all tests in Period A, I need the form field DateCompleteA to show the date that he completed the last one. (18/3/2015).
If student has not yet completed all tests in TestA subform the Form field DateCompleteA must be blanc.
Student has not completed "literature"yet, DateCompletA field is blanc
Student completed ALL his test, subform is updated, Form field DateCompletA is updated with the maximum date.
I have placed an unbound subform on a form. I have the following code which loads in different datasheet to the subform:
'Loads in NewQuery
Me.data.SourceObject = "query.NewQuery"
'Loads in NewQuery2
Me.data.SourceObject = "query.NewQuery2"
etc.... this works fine to show these queries but i want to know how to reference the fields on the datasheet.
for example one of the fields in NewQuery is "ID"... When clicked i want to run code... normally on bound subform i could go for example ID_Click()... msgbox(me.ID)
how can i reference these other fields on unbound???
I have a main form "KZ_SEARCH" and within this I have a subform which is loaded when a button is pressed and is populated with query results (based on text entered in a text box). The subform is called "KZ NOTICES Query subform". That all works fine and the query modifies based on the text entered every time the button is pressed. The next stage is that when the subform is loaded I want the user to be able to select a line from the subform and to jump to that record in another form (which also needs to be opened from that selection). The reference for the record to select in the newly opened form would be the first column of the selected line highlighted in the subform (control name "KZ NUMBER").
View 10 Replies View RelatedI was having a wee look around the Export Threads and came across the following VBA Code I thought would be handy to export a Query to a new Excel workbook, and although it did export to excel, it didn't filter the query to show only the things I was wanting. Now I've managed to have it not work at all!
Code:
Private Sub cmdOK_Click()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim strSQL As String
Dim i As Integer
[code]...
I already success to run this dynamic query where the parameters taken from the main form.Now the problem is the query result doesn't show in the subform.But the status bar below tell me that it have 2 records in the subform, but there is no data in the subform, it just Blank.I already apllied the Requery or Refresh to the subform (in the Command Button), but it have no result too.This is the code:
Forms!MsDataWarga.QueryDataWarga.Form.RecordSource = "MyQuery"
Forms!MsDataWarga.QueryDataWarga.Requery
Forms!MsDataWarga.QueryDataWarga.Refresh
I have a form called frm002_PAF_MonitoringMAIN and on this form I have a subform called frm002_PAF_Monitoring. The subform has a button to another form for contact details called Contact_Details. The Contact_Details form opens with the details of the person who I have selected on frm002_PAF_Monitoring. There are some fields on frm002_PAF_Monitoring that I would like to be populated on Contact_Details when opened, like FirstName and LastName. How can I do it?I have tried on afterupdate event on Contact_Details below but those don't work:
Me!FirstName=[Forms]![frm002_PAF_Monitoring]![FirstName]
Me!FirstName=[Forms]![frm002_PAF_MonitoringMAIN]![frm002_PAF_Monitoring]![FirstName]
Me![FirstName].Value=[Forms]![frm002_PAF_Monitoring]![FirstName]
Me![FirstName].Value=[Forms]![frm002_PAF_MonitoringMAIN]![frm002_PAF_Monitoring]![FirstName]
I have a query in my subform:
Code:
SELECT ContainsTracks.albums, ContainsTracks.tracks
FROM ContainsTracks
WHERE ContainsTracks.albums LIKE [VALUE IN TEXTBOX1 FROM MAIN FORM]
As you can see in the brackets, I would like to use the value in textbox1 on my main form in this query (so I can select only the tracks from the album that is typed in textbox1, disregarding the others).
I know how to use openargs to pass values between forms but im not sure how to go about passing all the values from one subform to another.
To be specific I have an order form (frmCustomerOrders) that has fields like CustomerName, EmployeeName, OrderDate and so forth, the subform (frmCustomerOrdersSubform) contains information on the Products the customer is ordering which usually has multiple records with fields like ProductName, PartNumber, Quantity and ItemNumber. I want to pass the values from the CustomerOrdersSubform form to another subform (TransferSubform) My initial thought was to use something like the openform command through openargs but im not sure if that will work or if there is another method I should use.
i have a main form with three sub forms on it. when i update subform 1 i want subform 2 and 3 to update..currently i have an after update event in a combo box subform 1
Code:
[Forms]![FRMDATESUM]![frmHOURSUMnowork].Requery
[Forms]![FRMDATESUM]![frmHOURSUM].Requery
when the user updates the combobox in subform one nothing happens to the other subforms until close and reopen main form
I almost have this form done.
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
I hope that someone can help with this!
:confused:
I have a form that is almost complete
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
I hope that someone can help with this!
I've got the following code on a form. The entirety of the sub is listed just in case. The relevant portion is below.
Code:
Private Sub Form_Timer() 'timer set to fire once per minute
'AutoPurge old records (>7yrs) and take action if Hotword and/or NANU expire
Dim Minute As String
Minute = Right(Format(TimeValue(Now()), "hh:mm"), 1)
'Only run these every 10 minutes
[Code] .....
The part I'm having trouble with follows:
Code:
If DCount("*", "qryNotifyExpiredNANU") = 1 Then
MsgBox "The NANU " & DLookup("NANUnumber", "qryNotifyExpiredNANU") & " has expired. It has been removed from the Active NANU list.", vbOKOnly, "NANU Expired"
ElseIf DCount("*", "qryNotifyExpiredNANU") > 1 Then
MsgBox "The NANUs " & !!!!RETRIEVED VALUE HERE!!!! & " and " & !!!!RETRIEVED VALUE HERE!!!! & " have expired. They Have been removed from the Active NANU list.", vbOKOnly, "NANU Expired"
End If
The intent of that (incomplete) ElseIf is to provide a list of all NANUnumbers that have expired. When a single NANU expires, the DLookup works perfectly since the query only has one record. But I'm stumped on dealing with anything more than one value.
The desired result is that the value from the NANUnumber field of each resulting record in qryNotifyExpiredNANU would be used to build a string for use in the MsgBox.
Using DLookup is out (I think) because I can't identify which records the query returned to apply a WHERE clause.
I also looked into using Loop with a RecordSet, but can't figure out how to approach it.
So to sum it up, I don't know how to grab each value present in the query and I also don't know how to concatenate a string with a dynamic number of values.
I have a query which is a concatenated field of text and then a field from a table that has a hyperlink.
i.e 15mm predesigned mould Drw: [L:10527-123-A.pdf#L:10527-123-A.pdf#]
I want it to display in the query field as just the hyperlink
15mm predesigned mould Drw: [L:10527-123-A.pdf]
I'm trying to write a query to allow the user to search through records.
To make the interface simple I just want a combobox to select the search field and a text box to enter the search string.
My problem is trying to build a query where the value of a combobox on a form is used as a field in the query.
I have a main form which does nothing except filter subforms through a cbo.
On the main form are two subforms. One shows top line data, the other shows a breakdown of the top line data, and are linked by an unbound textbox (it's how it works, and does so perfectly)
I'm running an append query to duplicate a record in the second form using vba/sql BUT... need to have one of the fields values changed based on a field on the parent table.
If Forms!frmmain!frmPost.Form.RecordsetClone.RecordCo unt > 0 Then
strSql = "INSERT INTO [tblposts] ( TopLineID, AccountID, TransDate, Cat, SubCat, Debit, Credit ) " & _
"SELECT " & lngID & " As NewID, AccountID, TransDate, Cat, SubCat, Credit, Debit " & _
"FROM [tblposts] WHERE TopLineID = " & Me.TopLineID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If
Credit and Debits are reversed as I want one to zero out the other.
In regards to the AccountID, I've tried allsorts and it's just not working... to the point I'm almost giving up and finding an alternative.
Whats the "Correct" syntax to attach a "WHERE" statement to the highlighted [AccountID]'s (which needs to be the value on AccountID on the Parent table)
I have a table 'table1' which has various field including an ID field and a yes/no field. I then have a form based on a query originally derived from data in the original table. The form provides a list with some ID's with a yes and some with a no.
I'm trying to write a button code to convert all the no's into yes' for those ID's picked out by the query.
I've searched lots of sites and get that I need to set recordsets for both table1 and the forms' query but all my efforts crash or give an error.
I am looking for the right way to say .." for each ID in form set the yes/no field corresponding to the same ID in table1 to yes"
I'd attach my version ....