Complicated SubForm

Hello, I have a main form with several combo boxes that are populated from 
Oracle linked lookup tables. I need to use a complex query with dynamically 
built WHERE clause to return results the user selects. I am having a hard 
time getting a subform to use this query. My code explained:

Main is the unbound "main" form that has the sub form "Main_Sub" embedded in 
it. 
I have VBA that builds a where clause based on user selection of combination 
of combo boxes and list boxes. 

my SQL statment - SELECT * FROM Table WHERE & strWHERE (dynamic where)

Main.Main_Sub.Form.RecordSource = strSQL - doesn't display anything in the 
subform ain1

The above code is in a command button on the main form. I used a Datasheet 
type form for the sub form Main_Sub. Any help is greatly appreciated.
0
Utf
2/29/2008 4:05:04 PM
access.formscoding 7493 articles. 0 followers. Follow

0 Replies
772 Views

Similar Articles

[PageSpeed] 34

Reply:

Similar Artilces:

COUNT Fields from Subform in Main Form
I am trying to count the number of parcels in a field called [ParcelQty] in my main form [Field Packs] from a field called [SBLPID] in my subform [Query.QrySBLFP] (Note: the table name is SBL FP). The Master and Child fields are FPackID. I used an example from the Help index and came up with this formula: =ParcelQty: (SELECT COUNT([SBLFPID]) FROM [SBL FP] WHERE [SBL FP].[FPackID] = [Field Packs].[FPackID]) It does not work in the form (I tried with and without the begining equal sign), so I tried leaving the form field unbound and using the ParcelQty field in QryFieldPacks. I get t...

Complicated pickle
I have a complicated (for me, anyway) problem that I have solved in a way that I'm not happy with. If anybody can offer suggestions for improvement to my methods, I'd very much appreciate it. The problem is that I've ended up with a set of formulas which fall apart if I insert a row. This is because I'm using the offset() function, where I suspect I should be using something else. The goal is to have 2 worksheets, named D (for data) and R (for report). The data is organized such that each column is a distinct entity. That is, the data points are rows and there can be hund...

Search not filter and display results in subform.
Hi, I have a small split DB BE and FE. I need to implement a search form where the results shows in a subform. I tried this http://allenbrowne.com/ser-62.html which is good but because there are about 2000 records and the BE is in a share drive it takes a while for the subform to load to show all records before I can filter. what I need is a "on the fly" filtering or search. I think I have to use SQL and build a query on the fly or is there a faster way? any ideas will be a appreciated. thanks! ...

Kill Focus on Subform
Hi, I have a main form with a bunch of subforms, let's call them A through G. Each subform has its own group of subforms, let's call them 1-4. So I have tons of subforms, like A1, B4, D3, etc. I have some code that cycles through all the lowest level subforms (like A3), and makes some visible and others invisible. I always hit an error that "You can't hide a control that has the focus." I have desperately tried to set the focus elsewhere so I won't get the error but it doesn't work. If I try to set the focus to a different subform I get the...

subform with 2 child/masterfields but one with expression?
Please Help! I'm trying to link using 2 fields but one of the fields is an expression. Table1 fields: License# (key) ccDate (mm/dd/yyyy) Table2 fields: LicenseNum enterYear (unbound textbox) I would like to be able to view only the certain year in my subform. And the user has the capacity to indicate which year (by using the [enterYear] textbox) to display. This is what I have: Link Master Fields: LicenseNum;enterYear Link Child Fields: License#,ccYear ccYear is an expression from a query --> ccYear:Year([ccDate]) The above works but each time I tried to enter/change new record...

some subforms and combos blank
When I open the fClosure form from a button on the f001ProjectReview form. It opens the fClosure form but some of the comboboxs and some of the subforms are blank. As in totally white. any ideas of why? If I open the fClosure form without using the button on the f001ProjectReview form it works fine. Here is the button code I use to open form Dim stDocName As String Dim stLinkCriteria As String stDocName = "fClosure" stLinkCriteria = "[ProjectID]=" & Me![ProjectID] DoCmd.OpenForm stDocName, , , stLinkCriteria DoCmd.Close ...

Refering to subforms on tabbed controls
Hi I have a main from on one tabbed page and a "daughter" form on the other. If I select a record on the main form, can I then switch to the second tab page and add records so that they are linked to the record on the first tab? Thanks I'm not sure I understand you. You can't have a "main form on a tabbed page". Any form embedded in another form, whether it is on a tab control page or not, is a SUBFORM. Do you mean that you have a tab control on your MAIN form, where one page contains data fields from your main form's Recordset and another page contains...

Requerying a subform from another subform
I need to requery (refresh?) a subform from another subform on the SAME main form. This syntax in the first subform doesn't seem to work. [Forms]![frm_Main_Form].[subfrm_Second_Subform].Form.Requery Can anyone help? Thanks in advance. -- Glenn Are you sure that the name of the subform control on the parent form is subfrm_Second_Subform? Depending on how the form was added as a subform, the name of the subform control may be different than the name of the form being used as a subform. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Glenn...

Complicated
In a sheet named "Confirmed" I have a spreadsheet which contains the following:- Column F (a list of languages) Column G (a list of languages) Column H (a fee per 1000 words) Column I (a fee per 1000 words) Now, I have set up a data validation series in a new sheet, so that when a language is selected, say for example, Arabic which is in F1 of the sheet "Confirmed", the languages listed next to it in G1 appears. If Arabic appears in F1, F2 and F3, then the languages which are listed in G1,G2 and G3 appear. Now what I want to do, is for in a new sheet in cell A1, I want t...

Subform of depreciation amounts
Good morning, I would like to have a subform on a form that list detail depreciation amounts. I know the purchase price of an asset, the assets life span and a depreciation start date. Is there a way for me to create records to populate a datasheet subform showing the depreciation amount? I was currently thinking of writing the transactions to a depreciation table, but thought displaying the information on the fly would be more useful. Is there a way to have a query create all these records and have that be the recordsource of the subform? Any examples or ideas would be most helpful. ...

Rule Too Complicated?
I want to be able to create a rule that when I send a message, a copy of the message is saved to a specific folder, and marked as read. Problem is, in Outlook 2003, I can't find the "mark as read" under the "check messages after sending" option Currenlty I've got a rule that cc's myself on the sent message. I'm then handling the received message "when it arrives" and moving it and marking it as read This is too time consuming, as I have a constantly changing list of folders and I don't want to set up 2 rules for each situation Any suggesti...

Default Value-Complicated
Hello, I have a Module that pulls the username of the person logged into the PC (if you would like to see this code, let me know). I have a table called tbl_WorkerIDs with all my staff's login Ids in a field called "LANIDs" and I have another field in the same table called "CSEIDs". Each person has a LANID and a CSEID. In a form I have a combobox field called CSEWorkerID. This combobox feeds on the above table. For the Default value of this combobox I need the CSEID to pull not the LANID which is the userid logged into the pc. On this CSEWorkerID co...

Subform sort order problem
I have a form/subform. They are linked on the field 'cnum'. The fields in the table 'Received' which is what the subform is bound to are: Cnum Ldate Lbs Furniture Clothes Council The 'OrderBy' = received.ldate DESC and 'OrderByOnLoad' = yes However, when subform opens the records are sorted in ascending order. To check this I put the following into the 'Current' event of the form: Debug.Print Me.OrderBy, Me.OrderByOn, Me.OrderByOnLoad The results of this are: ldate DESC False True ldate DESC False True ldate DESC Fal...

Can't assign value in subform
My setup: tblPeople, key PeopleID (autonumber), additional contact info fields tblSpeakers, composite key PeopleID and SpeakerID(text), additional fields Main form from tblPeople, subform tblSpeakers. When I first open the form, it works fine until I get to the SpeakerID field on the subform - I need to enter text to assign a SpeakerID, but receive the error message. I click ok, and am allowed to enter text. I checked and it is stored correctly in the tables. Subsequent entries are fine - I only receive the error on the first entry. What's wrong? I will need to use this for quer...

Disabling SubForm
On my mainform, frmEmployees, I have a control called Status. I have successfully managed to disable all the other ontrols on the form by tags and using the following code when the staus of a person is "C": Dim ctrl As Control If Status = "C" Then For Each ctrl In Me.Controls If ctrl.Tag = "Grey" Then ctrl.Enabled = False End If Next Else For Each ctrl In Me.Controls If ctrl.Tag = "Grey" Then ctrl.Enabled = True End If Next End If What I also want to do is is to di...

subform update?
I have a subform that allows a user to enter product orders. I also have a combo box from which the user can select from a list of vendors (from a table) and display the vendor's address in a textbox below the combo box. if a particular vendor is not on the list, the user may enter a new vendor by clicking a button and filling out a popup form. upon closing the add vendor form, focus returns to the order form where the combo box list is updated and selected to show the new vendor and their address below. the problem is when the combo box control on the order form is requeried to add th...

Update subform field from pop-up form
I have a Form with 2 subforms. I need the ability to change the Child Field on the second subform to a value found in a pop-up form. I have the following structure: FrmMain Primary Key: ClientNo FrmSubVisit Record Source: q_frm_sub_visits_active Field 1: Account Field 2: AccDate Link Child Fields: ClientNo Link Master Fields: ClientNo Default View: Continuous FrmSubEvent Record Source: q_frm_sub_events Field 1: Account Field 2: EventNo Link Child Fields: Account Link Master Fields: txtRelayAccount (text relay on FrmMain from frmSubVisit) Default View: Single Form The “Account” on frm...

complicated If statements
I am working with 13 subjects, each subject has three marks (3 adjacent cells, the third cell is the average of the previous 2) . Cells AX15, AZ15, BB15 will have a formula to show the name of the subject with the values of "F1" or "F2" or "F1, F2" depending on the marks in the cells. When AX15<>"" fill cell AZ15, if both cells (AX and AZ <>"" then fill in cell BB15) Example: H15, I15, J15 has 3 marks (for English) J15 =average(H15:I15) J14 has the 50% of the mark, I10 has the name of the subject If J15<J14 then if H...

combo box and subform help
In my subform for each seperate order, I want to beable to select a descripton name from the combo box. When the description name is selected, the price associated with that name should come up in the subform. I have tried to put the combo box in the table for description and make a query but, the information associated with the description name will not come up. Create the combo box cboItem with the RowSource something like: SELECT tblItem.ItemKey, tblItem.ItemDescription, tblItem.SellPrice FROM tblItem; Set the number of columns to 3 Set the column widths to 0;2;0 or something e...

Duplicating reocords from main form and subform receiving Runtime Error 3022
I am trying to develop a command button that will duplicate the main form and the subform information and save it to another record within the same tables. I used Allen Browne's program and modified it to fit my tables. At first it would copy the main form and not the subform data so I made some changes and now I am getting the Runtime error 3022. I have worked on this off and on for about 2 weeks. Any help on getting this to run will be greatly appreciated. Below is my code: Private Sub Command78_Click() 'On Error GoTo Err_Handler 'Purpose: Duplicate the main ...

Complicated Macro
I need a macro that will permanently delete all e-mails that contain certain e-mail addresses. This includes sent items, deleted items, inbox items, etc.. I was thinking a button I click at the end of the day OR for it to check whenever an action was taken (this seemed processor intensive). For example: ListOfEmails = {"bob@bob.com", "mike@mike.com", "john@john.com"} OnButton1.Click call ClearAllCommunication(ListOfEmails) Sub ClearAllCommunication(ByVal ListOfEmails() as string) '/ The hard part goes here. End Sub Thanks, J <jjanyan@gmail.com&g...

Clone Code Complication
Hi (Access 2007) I have the following code which works perfectly well for me on one form, though when I've tried to replicate it on another form I get a run time error 3077 - Syntax error (missing operator) in expression .... I hope you can help (they look exactly the same to me!) TIA. WORKING CODE Private Sub SearchCombo_AfterUpdate() With Me.RecordsetClone .FindFirst "JobID = " & SearchCombo If Not .NoMatch Then Me.Bookmark = .Bookmark End If End With Me.Notes.SetFocus End Sub NON - WORKING CODE Private Sub SearchCbo_Af...

making a chart complicated
I'm using Excel 2007 I have a chart that plots data points SUMPRODUCT(--(B498:Q534>0),--(B498:Q534<=100)/COUNT(B498:Q534)) plus other formulas (between 101 and 125 etc) Formulas are in the same column I need the chart to label the pie slice/column with 73% (83 "widgets") 73% (can do) but I haven't discovered how to add to the label the number of times that the criteria was meet ...83 times. Thanks for your help. I'm just a novice with excel. I have this data store sales North 25 widgets East 82 widgets West 34 widgets South 45 widgets...

subform used for changing mainform record
Hello. I have a main form that has a tabcontrol on it. One of the tabs has a subform on it that will list the many of the mainform's one. This is a parent/child hierarchy set up. So the parents and children are all in one table. A parent can also be a child and a child can also be a parent. Anyway, below is the code that I originally used for making the change. Now it does not and I do not see why. The button that I am using is inside the subform that when used takes and changes the mainform to the selected record and the subform as well. The txtChangeRequestID is the...

Editing Subform Data
Good Day.. I have made great progress with the application I have been working on but I believe I have hit my last stumbling block. The FoxPro application I am replicating displayed the queried data in a "datasheet" type "subform". One of the items the FoxPro application has is a "select" button which sends a copy of the data from the line selected in the "datasheet" subform to textboxes in the main form. Then the data can be edited and saved to the table with the changes. I have been advised that this is something that cannot be done in Acces...