I am working with textbox [DispCD] (a date field) on form [Contract]. There is a dropdown on combobox [Searchbox] for selecting the desired record. I am writing VBA in the [Searchbox] OnChange event. The control source for [DispCD] would normally be "=Searchbox.column(11)". If "column(11)" has a date, I want [DispCD] to show that date and then be locked. If "column(11)" does NOT have a date, I want [DispCD] to allow entry of a date with the date picker showing. I've tried to assign the control source to [DispCD] with this:
CSVal = Format(Forms![Contract]!SearchBox.Column(11), "ddd m/d/yyyy") (this works)
If CSVal = "" Then _
Forms![Contract]!DispCD.countrolsource = "=SearchBox.Column(11)"
I get "Object doesn't support this property or method" error.
In above example the criteria is based on [ProductID] on a form.Is it possible to change it, so criteria is based on a value from a specific query, i.e. similar as above?I have tried something like this, but it doesn't work:
I have an unbound form which has 2 unbound combo boxes on it, both based on the same table, but I have used a query for each one to created the sort order, one alphabetically by item name (cmbA) and the other numerically by the item number (cmbN) . I also have a text field (ID) upon which is based a report when the user clicks a button. Currently the text box (ID) has a control source of column 1 of cmbA and I have the report working perfectly if one selects from the name combo box (cmbA) What I want to do is allow the user to select from cmbN and have the control source of the ID text box equal the number the selected so the report button will work then also.
I have tried to set the control source after update of cmbN but all that does is blank out the ID text box. I have not done this before so I am sure I have done it incorrectly. I tried again this morning using me.ID.ControlSource = Me.cmbN and stepping through the code shows that the cmbN ControlSource is equal to ID but I can't make the code that follows use the results.
I currently have a DLookUp formula in a Text Box Control Source. It returns the value I am looking for, however, the value will not add to the record. I have tried putting the DLookUp formula in the Default Property, but it is not working.It is a multi-criteria DLookUp, where the criteria is entered into the form.
=DLookUp("[TBA CUSIPs]","[TBA CUSIPs]","[Settlement Month]=" & [Forms]![Enter Trade]![Settlement Month Text Box] & " AND [Coupon]=" & [Forms]![Enter Trade]![Coupon Text Box] & " AND [Instrument] = '" & [Forms]![Enter Trade]![Instrument Combo Box] & "'")
My form is based on a table (SOP TBL) and has several text boxes with control sources of the records in the table. I need to add a criteria to one field (SOP Number) so that only current versions of the number show up. I have the SQL for a query that I use in another form for a combo box as shown below. What this criteria does is find the version number which is the last 2 digits of the SOP Number and displays only that record for the SOP Number in the combo box. How can I apply this code to the text box with the control source directly from the table so only the current records are displayed on the form?
Code: In (select max([SOP Number]) from [SOP TBL] group by left([SOP Number], len([SOP Number]) - 2))
is it possible to use a sum select query in the control source field of a text box. I have a query that works fine in a combo text box but I keep getting a 'syntax error with subquery parenthesis' when I try and build on in a normal text box.
I am building the query using query design view and the query works and then I'm copying the SQL code behind the query into the text box field and access reformats it s a bit so not sure it's that's the reason.
R_P_Data_P = Table approvalNosys = Field in the table status = Field in the table score = Field in the table cmrOverview = Form [approvalNoSys] = field in form 1 is the criteria
Code: SELECT R_P_Data_P.approvalNosys, R_P_Data_P.status, Sum(R_P_Data_P.score) AS SumOfscore FROM R_P_Data_P GROUP BY R_P_Data_P.approvalNosys, R_P_Data_P.status HAVING (((R_P_Data_P.approvalNosys)=[forms]![cmrOverview]![approvalNoSys]) AND ((R_P_Data_P.status)="1"));
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
This is a very simple problem, and it seems to me like it should work but it doesn't. On my form, I have a field called [mindate], which looks up the earliest date from a table. I also have a field called [maxdate] which looks up the latest date in my table. These both work great. My third text box [difference] is supposed to find the difference between the two above dates in days, but it doesn't work.:eek: The control source of this [difference] text box is this:
im trying to add a text box for entering a date into the table database for my form but i cannot seem to connect the control source to the text box, the name of the box/script is not appearing in the control source list. I have already made a column in my table for this box on the form but just cannot connect the two properly?
I have the following tables and am trying to populate a text box on a form, and store that value in a field
TotalSegLengths
tbl_Segments LineNum SegLength 1 3 2 9 2 10
tbl_Lines LineNum TotalSegLengths 1 2 3
So when I create a new record in tbl_Lines from the form (Form is bound to tbl_Lines) it should query the tbl_Segments, add up the SegLengths for the corresponding LineNum and store that value in TotalSegLengths.
So for example, with the data as per above, from the form, create a new record with LineNum = 2 and it should display 19 in a text box and store 19 in the TotalSegLengths field.
I was trying to use DSum in the text box Control Source but was just returning an #Error in the text box.
I added a new field to one of my tables and query, but when I try to add a control for that field into my form it is not recognising it and the field isn't appearing in the sources for the form, even though the table is sourced to the form? Or am I going to have to do the usual and redo the entire form because I made a minor adjustment?
I have two queries called "Query_match_AND" and "Query_match_OR". I want to show the total number of records for either of these queries in a form called "form_candidates_result" depending upon the combo box value (either "AND" or "OR") in a form called "form_match".If I put
Code:
=DCount("*","Query_match_AND")
in the control source of [candidatecount] in "form_candidates_result", the value is shown in the form and it works fine.If I use the following code in either of the "on load" or "on open" events in "form_candidates_result"...
Code:
Select Case [Forms]![form_match]![ANDOR]
Case "AND" Me.CandidateCount.ControlSource = DCount("*", "Query_matching_AND") Case "OR" Me.CandidateCount.ControlSource = DCount("*", "Query_matching_OR") End Select
Hello, I've got a text box on my form header with a calculation in the control source that adds values from other text boxes on my form. My problem is that since the calculation is in the control source of my text box I can not save the sum of the calculation to a field on the forms bound table. Is there a way to move this calculation out of the control source and still have the calculation populate this text box with the sum of the calculation, so I can choice a field in which to save this calculations sum.
Calculation in the text box control source= [Tot40yrcomp]+[Totfelt1536]+[Totfelt3036]+ there will be many more other text boxes added.
I have 3 text boxes on a form each of which gets their values from different queries using DLookup this is functional but only when I click on the text box its self, I have tried using the "On load" & "On current" to requery the textbox (Text38 as control name) but no luck,, another strange thing i've noticed, when I have the form open in access and i click on my taskbar, thus switching windows from access to the desktop but keeping access in view, all text boxes are calculated immediately.
The code I am modifying looping through Me.controls collection to identify appropriate control types. Is there a way to influence the order (sequence) of that traversing? Or in different words, is there a way to assign by design certain item number to a given control?
I have a linked table in ms access and it has a column as details. in ms access when i click on this button i will go to another form. i want to assign a picture (for opening a form) to this column but i don't know how i have to do this. my form opens as a datasheet view.
What I want to be able to do is to assign a back colour to a control from VBA. There are many colours and what I thought would be an easy process was to choose a suitable colour to be assigned using the colour picker for a control, note the hex number then in my vba code would be
if... then me.mycontrol.backcolor=&HF0AD34
This compiles OK, but produces a completely different colour - I'm expecting an orange colour but getting blue and in another example a pale blue is coming out a brown.
Research indicates adding an ampersand to the end changes the value from integer to long but if I type
if... then me.mycontrol.backcolor=&HF0AD34&
The second ampersand disappears. In the immediate window
All produce the same value of 15772980, which if I copy back to the control backcolour property produces the blue colour and not the required orange - which if i debug.print me.mycontrol.backcolor give me 3452400 - the correct number for orange.
The problem seems to be converting the hex number to a long.
Also how to modify the properties window to show the decimal value rather than the hex value, but can't see anywhere to do this.
is it possible to set 2 control source to a textbox.i have a textbox in a form where it gets the value from subform =[Child9].[Form]![Text25] and i need to save it in a table in field INV_TOTAL
I have a form that uses a certain table(tblDetails) as the control source and within this form I have 3 combo boxes. One combo box looks up a company name from another table and I was wanting my second combo box [LastName] to have its look up list based off of the first combo box choice.
I have created a table for installer invoicing, and have a field for invoice amount. I have created a user input form that allows a user to fill in certain pay rates for different aspects of installation, and would like to know how to make the invoice amount a calculated control that will auto update the field InvoiceAmt in my table. I can't figure out how to do this in the property sheet.
I have a combo box control in my form named PayDateID, but I told that control to DISPLAY the associated field PayDate.
I now am trying to make a separate control in the same form whose control source is the DISPLAYED PayDate, but it only wants to reference the underlying PayDateID.
How can I make the control source be the displayed value in that combo box?
I'm getting a Name(hash) error when I try to set up a control source to a calculated query.
I'd like to just be able to create a form and the write in the control source as it look neater (and it the right way to do it) then subforming a query for one text box.
I presently have tried in the expression build =[qryCallRunningAverage]![Average]
