Forms :: DLOOKUP Needs To Reference Form Or Subform
May 16, 2013
I have a DLOOKUP which is used to auto populate a field on my Income Commitment Form based on a field on my Expenditure Commitment Form.It works perfectly when you enter a new commitment.Currently you enter a new Expenditure Commitment, save it but don't close it, then from the Expenditure Commitment Form there is is a button to enter a related Income Commitment. You save the Income commitment then close it, then close the expenditure form and you are on the Client screen with both these forms now showing as subforms, both have record selectors.
My issue is, that sometimes the Expenditure Subjective can change, so from the client form you use the record selector to open the Expenditure Commitment and select the correct Subjective, you then save it and close it. You should then click on the Income Record selector which opens the income commitment, but when you re-click on the Combo42 button, it doesn't reselect the Income subjective and I think it is because the Expenditure Commitment which is what it is looking up isn't open. My issue is how do I get the DLOOKUP to work and refer to the Commitment Form if it is a new commitment, or the Subform if I have gone back in through the record selector.Here is the code I have.
Private Sub Combo42_AfterUpdate()
If (Me.Combo42 = 1) Then
Me.IncomeSubjective = DLookup("[Subjective]", "tblIncomeSubjective", "[ExpSubjective]='" & Forms![frmCommitments]![cmbSubjective] & "'")
Me.OtherIncomeConfirmed.Enabled = False
Me.OtherConfirmedBy.Enabled = False
I have setup my main form with 2 subforms to mimic a split form; this works fine.
To search for a record, I am using a cbo on the main form which I would like the user to select from. Once selected, then this will populate the first subform.
This is the code I have for the cbo:
Dim intAnswer As Integer If IsNull(Me!cboCaseNoCFDWit) Then Exit Sub With Me!sfFocus.Form.RecordsetClone .FindFirst "Me!sfFocus.Form!CaseNumber = """ & Me!cboCaseNoCFDWit & """" If Not .NoMatch Then If Me.Dirty Then Me.Dirty = False
[Code] ....
I get an error at the .findfirst; states that the Microsoft Engine does not recogize Me!sfFocus.Form!CaseNumber
And the reason I am not using a regular split form is because I cannot, get the form to the size that I want. The splitform works great with the code above (a few changes to it of course), but the bottom of the splitform (datasheet) is too long and I cannot shorten it.
I have a Main form 'frmEmployeeInjury' with a subform 'frmInjuryDetails'.On the subform is a date field 'dteDateofInjury' and on the main form a command button 'SaveRecord'
What I am trying to do is hide the command button on the main form until a date is entered in the 'dteDateofInjury' field.I have looked online and found information on main form and subform referencing, but I can't follow it very well without examples.
Code:
Private Sub dteDateofInjury_AfterUpdate() Me.Forms![frmEmployeeInjury]![SaveRecord].Enabled 'Me.Parent.cmdSaveNewInjury.Visable 'Me.Parent.cmdSaveNewInjury.Enabled End Sub
I have the following bit of VBA which works like a charm :
Code: With CallNotes.Form.RecordsetClone .AddNew !CustomerRef = Me!CustomerRef !Who = Forms.markswitchboard!Text52 !Date = Now() !Notes = "Final Deposit Due on" & " " & FinalDepositDueDate !DiaryDate = FinalDepositDueDate + 1 .Update End With
However, if I try to use this code on a different tab control it just doesn't work.I get Runtime Error 424 object required.I guess I am asking how to reference one subform from another?
I have form named SearchForm and there is unbound subform named Search_blank. Depending on button in menu is used source object of Search_blank. One of the source objects is form named MeasureForm and it has subforms named MeasureForm_sf1 and MeasureForm_sf2. How can I reference public sub in MeasureForm_sf1 from SearchForm? How can I reference public sub in MeasureForm_sf1 form MeasureForm_sf2?
There might be a problem with unbound form. I tried reference public sub named GetLastVal from SearchForm that is in MeasureForm:
Code : Me!Search_blank.Form.GetLastVal
and it was working. Than I tried reference public sub named GetLastVal2 from SearchForm that is in MeasureForm_sf1:
I'm new to access and need to modify an existing database to get a report I want to create. I'm an Excel user and could accomplish what I want to do in seconds using Excel's Lookup functions, so I'm trying to do the same with Access Dlookup.
I have two tables in my database that tracks components in a hardware product. The first table is just a list of hardware units shipped. The second table is a list of various components (and versions thereof) used in each unit. The two tables are linked by a UNIT_ID field. I need to create a report showing each unit and what the various versions of the components are. My difficulty is that the component table is does not have a dedicated field for each component and each unit can be comprised of a different quantity of components. So the tables look something like this:
UNITS TABLE: ID Customer 1 Joe 2 Bob 3 Bill
COMPONENTS TABLE: ID Component Revision 1 Main Board A 1 Blue Board B 2 Main Board B 2 Blue Board B 3 Main Board A 3 Red Board C
I want to produce a report that looks like ID Customer Main Board Blue Board Red Board 1 Joe A B -- 2 Bob B B -- 3 Bill A -- C
I setup queries to get revisions of each Board and am trying to compile into the full report, but can't figure it out. Right now I'm trying to use Dlookup to lookup data from one of the queries and insert into a larger table. I can lookup a single value fine, but can't figure out how to get Dlookup to give me the Blue Board & Red Board values for each of the system ID numbers in a master query (which would be a snap in Excel).
I have a subform that has the Expense Code from the Expense Codes Table and I want to look up the Description (yes I know its not correctly spelled in the formula but it is in the table)
I'm working on my first report and am trying to get my textbox, tboJob to show a field value and not the ID. I have tried the following Dlookup in my control source with no luck.
Here goes my problem, and how I am attempting to solve it, but I am open to advice.
I have a table that contains all of the bird species in the US; (fields: common name, scientific name, species ID) I want to be able to update another table, (birds to chase) with particular bird species that I have not yet seen, (fields: species ID, common name, scientific name) and link that to a sub form that links to another table (chase birds locations) which shows geographic location data as to where I might be able to see new/unseen birds; (fields: species ID, date, other geographic data).
My problem is this. I created a form for the unseen birds table that allows me to only select a bird using a combo box of birds from the larger table of US birds. I want to select a bird using the common name in this box, and have it automatically populate the scientific name and the species ID into the birds to chase table. I link this tables to the location table using species ID.
A workaround that I have used is to close the form, run two update queries that populate the proper fields for scientific name and species ID and to re-open the forms. Although this works, when I return to the form it does not return me to the proper record, so that I can enter data into the sub-form.
Obviously this is a workaround and will work for me in the short run, but I want to learn how to do it properly within Access 2003.
I'm not a programmer and hence do not have access to the .NET tools or Visual Basic. Any hints for me out there?
I am trying to see if it is possible to store colors in a form or table and then reference them while in VBA. What I am hoping to do is when I write all my code for command buttons to change On Got Focus, instead of writing xxx.backcolor = RGB (255,255,255) i could do something like xxx.backcolor = Forms!HiddenColors!Command That way if i want to ever change the color scheme of the db, I can change it in one place rather than hunt lines of code.
I have played around but with no success. Technewonline is a website that specializes in introducing the latest technologies such as Best Tablet Android Have Price Under $200 and Best tablet of Apple in 2014 and The Best Midrange Smart Phone In 2014 and Top Best Ultrabook Of 2014 and The Best Phones 4G Valued At Under 300 USD is also a website for sharing your tips about computers, mobile phones and tablets, products are available from leading supermarkets will surely satisfy you.
I have a form with two subforms (the second is the child of the first). I want to filter a combo box in the second subform using the record of the first as the filter.
When I open the form from the object list I get this '2455' error. When I press "end" and the form opens the requery does what I want it to do with no further error, using this code in the "got focus" event of the combo box in the first subform:
In my form (source tblJobs) i input a contract number from tblContracts. but i would also like it to show the contract address when the contract number is entered. Both fields belong to tblContracts but i only wish to enter the contract number, the address will just be used as a reference as it isnt something i would have to enter again.
So for example
112 - Main Road
If i enter 122 in my ContractNo field i would like it to show the contract address for that specific contract.
I have a form (frmSWL) with a subform container on it (subfrom1) which has a source object called frmSite. How do I reference frmSite from another form so I can apply a filter to it??
I'm making a form for colleagues to use which will eventually populate a table. I'm using DLookup to populate some of the fields in the form from another table. The problem is that for a few of the fields in that other table, the values are incorrect.
(Obviously the most sensible thing to do would be to amend the source data but for various reasons I cannot do that.)
For example, "date built" in the original table might be "f" but that needs to be changed to "h", so I tried the following but it doesn't work: .................................................. ............................................
If DLookup("date_built", "dbo_meas_questionnaire", "[meas_id] = Forms!FrmRetestSearch.numOrigId") = "f" Then Me.TxtDateBuilt = "h" Else Me.TxtDateBuilt = DLookup("date_built", "dbo_meas_questionnaire", "[meas_id] = Forms!FrmRetestSearch.numOrigId") End If .................................................. ..................................................
I have 10 fields on a subform. they are named value1 to value10 consecutively. I would like to loop thru them, and get their values to use in subsequent events. I tried the following to reference them...
Dim iLoop as integer Dim fldVal as control Dim ItemValue as string
For iLoop = 1 to 10 fldVal = ("Forms![Form 1]![Subform 1]!value" & iLoop) ItemValue = fldVal Next iLoop
I also tried a couple of other variations, but cannot get this **** thing to work. Any help would be appreciated.
How do you refresh all of the data on a form when the tables that are the source for dlookup fields on the form are changed by command buttons on the form?
I'm trying to reference a control in a subform from a module but I keep getting errors!
Here's what I've tried.. and what error I've been getting:
First try: GetCboEntries = Forms.frmMain!frmSub.Form![cboEntries].Column(1) (where frmSub is the name of the actual form in the subform control) Error: Object doesn't support this property or method
Second try: GetCboActivities = Forms.frmMain!chdMain.Form![cboEntries].Column(1) (where chdMain is the name of the subform control) Error: Object doesn't support this property or method
Third try: GetCboEntries=Forms!NameOfMainForm!NameOfSubform![cboEntries].Column(1) Error: Run-time error '2450': Microsoft Access can't find the form 'frmMain' referred to in a macro expression or Visual Basic code.
Trying to run an append query for a specific record on a subform but won't recognise the subform control.
The Master form is fmSickEdit do I have to reference the Master file in the SQL?
The SQL for the query is:
INSERT INTO tblDisLetter ( SickID, DateAdvisedDisciplinary ) SELECT tblSick.SickID, Date() AS [Date] FROM tblSick WHERE (((tblSick.SickID)=[Forms]![fmsubSickListEditVersion]![SickID]));
I have a form which have a sub form. In the sub form I have a the direct table to update data on it.
Example:
The form has the name of the project and the subform the name of one of the items of the same project and the table present the documents for the particular item.
The users have the option to add or edir docs to this item.
If I would like to reffer to a value they are entering on this table in VBA . how can I do that?
(I would like to present a msgbox when they are entering a date that isn't right on the table for this item.)
By the way, I can't use validation rule because I have a different dates needed per item.
I have a form with a subform. I would like a control in my parent form to uncheck a checkbox control in my subform when that field value is deleted . I can get that to work if there is only one record in my subform but it doesn't uncheck the rest. how to I reference all the records in my subform so all of them will uncheck when I delete that field value?
So I'm working on something for housing. Each house has a "Property Reference". This property reference links to all other information on the property.
There's two tables, Referral (For a tenant, with the Property Reference as a foreign key) and PropertyInfo (Holding all the property information)
Basically I want to save users as much input time as possible, so I'd prefer if they could just enter the property reference for a person, and that populates the rest of the table.
I'm currently using DLookup on the main form where it displays the information related to the property reference, obviously it's not actually being saved into any fields.
Will this method be okay if I would want to search the records by the address on the main form?
I have a field on a form that is the results of a dlookup to a table.field. I want the value to be updated when I execute a button that is running a macro. How do I do this automatically, and not have to hit the refresh button?
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").
