I would like to update the SQL for a subform. I have an SQL statement that I have placed into a string and now I would like to update the subform using it's RowSource property, but I get an error when trying to use the following statement:
Forms![frmModelSales].[frmModelSales-Subform-
QuestionableShip].RowSource = strSqlForSummary
(the message board has split this into two lines, it is really one)
I've go a subform with 2 comboboxes. It's a datasheet view.
The combobox controls are called cmb1 and cmb2 for the sake of this...
cmb2 is dependent on cmb1. So when you select a certain value in cmb1, cmb2.rowsource should get updated to use a certain table.
But the kicker is that this should be on a per row basis. So if on row 1 of the subform datasheet you set
cmb1=Manager, cmb2.RowSource should get set to 'tblManagers'.
Then on the next row, cmb1 is set to Directors, cmb2.RowSource should get set to tblDirectors.
But the cmb2.rowsource on row 1 is still set to tblManagers.
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 have a form with many comboboxes. The comboboxes need to have their original rowsource set to a table in my database. However, when the user selects a value in a combobox, it is programatically fed into a SQL string which serves to populate a subform. This part all works great.
However, I'm trying to reset the comboboxes as well so that options will "narrow down" until only a single option is possible. So far, I have:
Code:
Private Sub cmb_Author_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode,
[Code] ....
GetWhere() is the portion that feeds info from the comboboxes to the Finder string.
The red highlighted bit is the part I'm working on. Obviously, once I figure out the proper syntax, all of the other
comboboxes will be set up the same way.
What's funny is that in testing (all of the data currently input is simple and allows me to select options where I know what the response will be in other boxes), when I change Author and go to use cmb_RecordName, it replies that "the record source 'My Data' specified on this form or report does not exist" (where 'My Data' represents real data).
So, clearly, it is finding the data, just not using it as a record source, but rather believing that the name of the recordsource is the value.
I have a form that contains a combo box, I used the access wizard to make this combo box select a record from a table of customers and display that record on the form.
The user then has the option to print that record.
This works fine.
I now want to be able use the same combo box but with a different table so I can select Records from a list of suppliers.
Is there a way I could use a toggle button to change the rowsource of the combo box and then use that same combo box to select records from my supplier table.
How could I write vba in order to get one field in a subform to update another field in a different subform?
View 2 Replies View RelatedI have a combo box which I define a SQL statement as its rowsource.
When I open the form, I have yet to define the SQL statement for the combo box.
My code is:
if me.cmbobox.listcount = 0 then
me.cmbobox.enabled = false
else
me.cmbobox.enabled = true
end if
But it shows the box as still enabled.
When I do debug.print me.cmbobox.listcount, it shows a value of 1. But that is impossible since I have yet to define a rowsource for it or its empty.
I also have column heading turned off too.
I have a 3 column list box in the format:
A1 A2 A3
B1 B2 B3
C1 C2 C3
The rowsource for this contains A1;A2;A3;B1;B2;B3;C1;C2;C3
I want to reverse the order so I have
C1 C2 C3
B1 B2 B3
A1 A2 A3
Which would give a row source of C1;C2;C3;B1;B2;B3;A1;A2;A3
I'm developing a form to link a clause to one of a selection of internal documents from drop down boxes (of which there are a lot and hence looking for ways to make it more manageable to find).Now the setup of the internal documents is unfortunately beyond my control (separate SharePoint Libraries, inconsistent fields, you name it they've picked the worst way to do it :P).
I figure the best way to do this is to set up some unbound controls - two drop down (to narrow down location/department), two text boxes (doc code and name) with the idea that it will limit the selection to anything 'like' what is typed in.What I would like is either: How to set the RowSource criteria to only apply if the relevant control is not blank (i.e. if all the boxes are blank, all docs will show; if 'Sales' is selected in department and the user types 'Sales Order Entry' only the Sales Documents relating to order entry will appear)How to add criteria to the RowSource of a dropdown box via VBA (I've created the relevant 'If then ... else' statements but not sure how to add rowsource criteria in VBA)
I have a form that contains a subform. I want to make a button that on click updates all the records listed in the subform. This is the best I came up with.
Dim rs As DAO.Recordset
Set rs = Me.SubList_for_Billing_Center_Form.Form.RecordsetC lone
With rs
.MoveFirst
Do While Not .EOF
.Edit
UPDATE Billing SET Billing.Billing_Declined = True, Billing.Billing_Declined_Date = Date()
[Code] ....
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.
As the unbound parent list box record selection is changed, I want the sub form to refresh. Do I put code in the Subform Current Record event?
Master Form Name: frmsr_NewWellEntry
Unbound Listbox - when record selected the primary key populates txtNavWellID (unbound) on parent form
subform Container fsubsrNavSHLBHL Link Master Fields =Forms![frmsr_NewWellEntry].[txtNavWellID]
The read-only form in the subform Record Source is something like: select * from vsrNavigatorSHLBHL where Well_ID =90243..Key The Form used as the subform above will be re-used in multiple parent forms. The parent form data is form SQL Server, the subform from Oracle.
My subform consists of a list of tasks that are waiting to be verified. in order to verify tasks, the user scrolls through the list of tasks and checks a checkbox (discrepancyverified) on each record they wish to verify. After the user has finished checking all the records they wish to verify, they click a verify button on the main form which should then go back through each record and update the verifieddate value of any that are checked to today.
This is what I have so far:
Code:
Private Sub Command19_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
[code]...
I want to create a different rowsource-query for a lookup field (field1) in each record in a subform. The rowsource changes dependent on the value in another field (field2) in the same record. How can this be done?
- I tried to change the rowsource-query in an eventmacro when the focus is set to field1, but this ofcourse changes the rowsource for all field1's and makes the allready selected values unvisible.
- I think I have to include the value of record 'field2' in the rowsource query, but i cannot find a way to include that value in the query.
Something like:
Lookup field1 in the subform contains this rowsource
- SELECT CUSTOMER.Id, CUSTOMER.AGE, CUSTOMER.NAME
FROM CUSTOMERS
WHERE (CUSTOMER.AGE= me![field2]);
me![field2] however does not function
I am working on a database project where I will Select a record on a subform
See the picture attached.
I know that using DAO recordset, i can update the checkbox one by one, but it become way too long when I have thousands of record...
May I know if there is a way to Select entire column of the subform?
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.
Hi All,
I have a question:
I have 3 forms (a,b,c). Forms A and B are seperate forms and C will be activated by either A or B. The recordsource of C is a query which can be:
[forms]![formA]![text1] = Qr1
[forms]![formB]![text2] = Qr2
How can I write a code (or adjust it in the query) that that lets form C change it's rowsource in Qr1 or Qr2?
hi everyone,
i'm new to this forum.
I have a drop-down list that I want to populate from one of the columns in a table. But i want to do this with VBA, instead of building the query.
The reason for this is that I'm saving the project as an MDE file, and my database is residing in another *.mdb file. Thus the MDE file is just the executable version.
I can't seem to "read" the table without using VBA to do it.
Can anyone help me please?
thanks.
Hi,
I create the the combox in the form.
The Enter Event of the combox is
name.rowsource = "(select 'ALL' From [tbl]) Union (select [Name] from [tbl] Group By [Name] Order By [Name])"
The list solutions are:
Alan
ALL
Amy
Jeff
....
...
Can I put "ALL" in the first postion?
ALL
Alex
Amy
....
Because "ALL" means all the values.
I want to put "ALL" in the first place.
Thanks.
following problem:
1) a textbox gets value from a combobox
2) after selection in combobox value exists in table but isn't shown in textbox
until form is closed and newly opened
-> how do i requery so that the value that is existing is shown instantly??
I have the following code in the second combo box on a form
Me.cboReportType.RowSource = "select fldReportTypeID,fldReportType, fldFrom, fldTo, fldDue from tblReportType Where fldReportTypeID Not in (select fldReportTypeID from tblReport) order by fldReportType"
right now this code is filtering so that once a specific reporttype is used that specific reporttype does not appear again as a choice HOWEVER i need it to do this for a specfic record chosen in the first combo box...at this point once a reporttype is used for one record it does not appear again for any record -
the first combo box rowsource:
Me.cboProject.RowSource = "select fldProjectID,fldProjectNo, fldTitle, fldPurpose from tblProject order by fldTitle"
any help is appreciated
I know that for 1 column in a combo box you can say cboBox = a;b;c;d;e;f; But for my situation i need to have multiple columns... Does anybody know what the string should look like for multiple columns of values ?
View 4 Replies View RelatedI am trying to retrieve a record from a table (M_Paint) using a text box (txb_oldrecord) and a command button (cmd_getrecord). I want the record to show up on a list box (lst_display). I have written the following code:
Code:Private Sub cmd_getrecord_Click() With Me.lst_display .RowSource = _ "SELECT [M_Paint].[Old_Code], [M_Paint].[Supplier], [M_Paint].[Old_Color], [M_Paint].[Metallic], " & _ "[M_Paint].[Color_Number], [M_Paint].[Finish_Comments], [M_Paint].[Size], [M_Paint].[Number_of_Samples], " & _ "[M_Paint].[Project_Number], [M_Paint].[Date_Received] " & _ "FROM M_Paint " & _ "WHERE [M_Paint].[Old_Code] = '" & Me.txb_old_rec.Value & "';" End With End Sub
When I test this, I get the following error:
Quote: Data type mismatch in criteria expression.
What am I doing wrong?
Thanks in advance!
Ive posted similar in the forms section but it has gone unanswered so I've pulled out the offending query and hope someone here can offer insight into this frustrating problem.
I have a form [Training Record] that has 2 combo boxes.
Box A (course_title_combo) has the titles of the various training courses. It pulls 2 columns from the training_courses table, namely courseID and course_title. Only course_title is shown in the box. The control source of this is set to CourseID as this is the field to be written to the training_record table, showing who attends what course.
Box B (course_date_combo) is MEANT to pull through the dates that each course is on, by pulling through the course dates for each course where courseID (as selected by combo box A) is equal to the courseID in the course_dates table.
Here is the current rowsource query for box B - which is basically showing blank fields...
SELECT tbl_course_dates.course_date, tbl_course_dates.CourseID FROM tbl_course_dates WHERE forms![training record test form]!course_title_combo=tbl_course_dates.CourseID
I cant see what is wrong with the query, but Im guessing it must be wrong as it is the rowsource which determines what is included in combo box B.
Any help would be appreciated no end - im totally stuck now :(
Hi. Basically, I've been having trouble with this for a while now. I thought I got it working yesterday, but it turns out not. Here is the situation.
I have built a form that contains several combo boxes and a few text boxes for input. The user enters some or all of this information and clicks "Generate Report". Upon clicking this button, the form needs to launch a generic report that is populated with data using the search terms specified in the form. However, to add complexity, only certain parts of the SELECT and FROM clauses can exist depending on which options are completed on the search form.
I tried: DoCmd.OpenReport stDocName, acPreview, sqlQuery, whereCond
Where sqlQuery changed based on which settings were specified. However, this did not help because the actual query that belongs to the Report never seemed to change: sometimes it was missing columns.
I tried also to set rptNominationsList.RowSource = sqlQuery, but that also doesn't seem to work. On this one I get "Object required".
Thanks in advance!
Alex