Populating a subform from a listbox

Hi all,

Having spent the last couple of days banging my head against my monitor
trying to work this out - I thought I would turn it out to the gurus here.

I'm working with a form (FrmClientEnquiries) which will populate records in
TblClientEnquiries.
Based on the user's selection in a listbox of 'RequestType' (LboRequestType),
I need the subform (sFrmRespChecks) to populate a series of records based on
the various 'Resp' asociated with the 'RequestType'.

Tables include:

TblRequestTypes
    RTID (PK; autonumber)
    RequestType (eg: CID, Doc Check, Account Name Change,...)

TblResp
    RID (PK; autonumber)
    Resp (eg: Client, RM, RM Support,...)

TblRRLinks
    RRID (PK; autonumber)
    RequestType (Sourced from TblRequestType)
    Resp (Sourced from TblResp)

TblClientEnquiries
    CEID (PK; autonumber)
    RMSTSubteam
    Client
    Administrator
    RequestType
    ...

TblRespChecks
    RCID (PK; autonumber)
    CEID (sourced from FrmClientEnquiries)
    Resp (Sourced from TblRRLinks)
    Completed (Yes/No; Checkbox)

****************************************************

Code currently being used (I picked up & worked from John Vinson's "Animal
Clinic" sample which has been posted here a number of times):

Private Sub LboRequestType_AfterUpdate()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrSQL As String
Dim StrResp As String
Dim StrRequestType As String
Dim IntRTID As Integer
Dim IntRRID As Integer

If Me.NewRecord = True Or Me.RecordSource = "" Then
    Exit Sub
End If

Me.CboRequestType.Requery
Me.sFrmRespChecks.Requery

Set db = CurrentDb

StrSQL = "SELECT RRID, Resp From TblRRLinks WHERE " & _
"RequestType = '" & Me.[CboRequestType] & "'"

Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)

With Me.CboRequestType
    For IntRTID = 0 To .ListCount - 1
        rs.FindFirst "[Resp] =" & .Column(1, IntRTID)
        .Selected(IntRTID) = Not rs.NoMatch
    Next IntRTID
End With

rs.Close
Set rs = Nothing
Set db = Nothing


End Sub

****************************************************************

Alright - so the code runs quite happily, but I'm not getting anything with
regards to having sFrmRespChecks being populated when the user selects a
RequestType from the listbox.

Any thoughts/guidance with this are greatly appreciated.  I've been running
around in circles for the last couple of days trying to get this ticking over.


Many thanks to everyone.

Matt

-- 
Message posted via http://www.accessmonster.com

1
MPWoodhams
10/2/2007 10:00:07 PM
access.formscoding 7493 articles. 0 followers. Follow

0 Replies
457 Views

Similar Articles

[PageSpeed] 56

Reply:

Similar Artilces:

Listbox selection, unselected when opening Internet Explorer (NO S
Hi TWIMC, Yes I know it's a very odd questions but I have a list box on a form in Access 2002 and when the form is opened the list box is populated with worksheet names from a selected default workbook. When I select the worksheets names that I want to update, (usually all of them), the selection shows all items selected, now when I open up a new session of Internet Explorer (version 6.0,2900,2180 and no I can't upgrade any software, it's on a work machine.) the list box is updated (but doesn't trigger the afterupdate event, odd!) and no items are selected. I've eve...

Fields in Main Form to Populate Records in Child Form
I am working with a local non-profit that works with Seniors and am trying to revamp their database. I have created several tabs for each type of event the volunteers or receipients may particiapte in (Education, Travel, Donations, Committees, Teacher, etc.). I want to be able to create the input in the main form where they can input there, click ADD and have it populate the child form that is in a datasheet view. I want to make the child form read-only so the volunteers that input the data can't accidentally overwrite any existing data. My thought was to create a "...

ListBox (maybe?) ... If so, how?
Excel 2000 ... Earlier today I saw a spread sheet that had what appeared to be a Filter Switch in a Cell. When cell with switch selected ... switch listed 2 values to choose from (Y or N) ... In addition, as different cells (requiring same Y or N value) were selected the switch moved to selected cell. Above said ... how is this all accomplished as I wish to replicate this capability on another spread sheet? Thanks ... Kha Ken, What you describe sounds like Data Validation. Check out "Designate valid cell entries" in Excel Help. David "Ken" <anonymous@discu...

? Embedding [ListBox/Edit] Controls in a List Control
Hi, I need a way of putting List/Combo boxes as well as edit controls in a CListCtrl. I do not need them all to be visible at the same time, rather, I need the list control to display regular text in all the cells normally, but when an item is selected to show the listbox/edit control in the cell. I've looked around for a good implementation but cannot find any really good ones; the best one was XListCtrl on CodeProject, but that does manual drawing of the listbox which is not the best thing, and it does not support edit controls. Does anyone know what the best-fastest, mos...

Validation and subforms
I have a form with a subform. I am using validation in the form's Before Update event to assure required fields are filled in. However, when I click into the subform the record is saved, and the Before Update code runs before I'm ready for it. Is there a way, short of hiding the subform, that I can allow the user to click into the subform without producing a message that a required field is missing a value? The logical flow of things is to select the subform values fairly early in the process of entering a record, rather than after everything else is complete, which is why h...

HELP with Subform Coding
I have two unbound fields on a form that I use for Search boxes and two subforms forms linked to its corresponding Search box. "Search" and "SearchJ" and linked with Child Field (Number ID) and Master Field (Search) and Child Field (JNumberID) and Master Field (SearchJ). On the After Update Event of both Search boxes, the cursor moves to the respective subform anfd retrieves the correct record. I would like to ELIMINATE one of the subforms and USING CODE change the Child Fields and Master Fields based on which Search Box I am using. I tried using IF statements in t...

subforms 08-13-07
I have an access 2007 db with three tables, and each table has its own form for data entry. They are not linked together. Is there a way to create a fourth form that calls out the other three forms and the data entry person(s) only see the forms? ...

How can I "undo" an entry on a form/subform while still entering data ? 11-18-07
Hi I posted this question a couple of days ago and got 1 response but couldn't read it. Hopefully someone will have and answer. I have a form/subform for entering invoice data. What can I do if I realize that I have already entered the invoice ? Example: I enter the main form data and enter 5 lines of detail on the subform and then I realize that I have already entered this invoice before ? I would love to have a button that I could click that "undoes" the entire entry. How can this be accomplished ? Thanks Mark <mthornblad@gmail.com> wrote in message news:81271...

Listbox contents do not show..
Hi (Access 2003..), I have an unbound listbox I use to get some info from a user (by merely selecting a row). The listbox, and its contents show properly. There's an "after update" event for this box where I gather and analysze some info, such as the row number and the content of various columns. So far so good. I've recently added some code that sometimes issues a warning message (based on circumstances). This is done as a msgbox with some verbage and which simply contains an OK button. The problem is that when the msgbox appears on the screen, the entire content...

VBA listbox
Does anyone know how I can highlight the last selection of a list box when the form initializes? I made a 4 item listbox with a For Next (i) loop that reads the items from a range in Sheet2. When an item is selected I put it in another cell on Sheet2 that will be read into a text file for later use. I want that last selection to be highlighted the next time I run the form so the user knows which item was selected the last time. Any ideas on how to do it? Thanks. Fred. Hello Frederick Depending on whether you wish to keep this value even after closing your workbook, here is some way t...

attach a file to a subform
Hi, I would like to add a button (outside of the subform) or a double click on text box (inside a subform) to add an attachement (pdf, word, excel). I was told i can use the OLE Object, but I could make it work. thanks in advance -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200705/1 I meant I could not make it work ielmrani wrote: >Hi, >I would like to add a button (outside of the subform) or a double click on >text box (inside a subform) to add an attachement (pdf, word, excel). I was >told i can use the OLE Object, but...

Calling HTML Help from subform
I'm working in ACCESS 2003 on Windows XP. I converted our .hlp help file to ..chm and I am using the code from Microsoft's web page at http://support.microsoft.com/?kbid=275117 to call it. Our .chm help file opens fine for all forms and controls except for subforms which are contained within main forms. Can anyone tell me how to change the code so it (the help file) opens when the focus is on a control in a subform? When the focus is on a control on the main form the code works but not when the focus is on the subform. I have context IDs on all the form and subform controls. Th...

Access 2007 Run-time Error 2467 when accessing a listbox value on a sub form
I have a database that has been in production on Access 2003 for quite some time and has run without errors. A user's machine was recently upgraded to Access 2007 and now the database throughs a Run-time Error 2467 "The Expression you entered referes to an object that is closed or does not exist" on the following line that references a listbox value on a sub form. If Nz(Me.sfrm_Consultant_Contract_Request.Form.Cmb_Consultant.Value) = "" Then .. .. .. I changed the code to If Nz(Forms! frm_Main.sfrm_Consultant_Contract_Request.Form.Cmb_Consultant.Value) = ""...

Auto Populating cells from a master spreadsheet
How can I autopopulate a cell from a master spreadsheet to other spreadsheets while maintaining all data and cell characteristics(cell color, attached notes, etc.)? ...

ListBox 04-06-10
Hi I like to try and get help on this again I know the problem my lack of computer experience means that I only explain in the terms I understand which is often not the correct terminology which is no help to you experts to try and give some help. So I am going to explain what I am doing step by step and only move on when you understand me. I have a folder called MyMusic this contains about 700 folders for each artist. the artist folder has a list of songs I open C:\ Drive use Views to display by details If I click the name tool bar I can select the columns to display like A...

How to add record in Subform which is in another Subform
Hi all, I have Subform "SubA" on another Subform "SubB". And "SubB" is on Form called "Main". On "SubB" I have button called "Add New Record" and I got macro (see below) on this button's click event. Private Sub Command1_Click() Me.SubA.SetFocus DoCmd.GoToRecord , , acNewRec End Sub When I open Form "SubB" and click button, macro works but when I open Form "Main" and click button which is showing in "SubB" subform, macro don=92t work. I tried below macro but I don=92t work Private S...

Listbox scrolling with the mouse
I'm using a clistctrl with in-place editing. The examples I have found on internet is using a CComboBox when a combo is needed for in-place editing. I have however found another combobox that looks a lot better and is really easy to configure so it behaves the way you want at http://www.codeproject.com/listctrl/propertylistctrl.asp. It's called InPlaceComboBoxImp. The CInPlaceComboBoxImp class is derived from CWnd and in its ::OnCreate() function it creates an editbox, a button and a listbox. The problems starts when there is many items in the listbox so you have to scroll. Sc...

transparent/colored listbox in mfc
if you want to create a listbox in transparent mode follow these steps: 1. The listbox resource needs the LBS_OWNERDRAWVARIABLE property 2. Create a new class derived from CListBox 3. You only have to add two virtual functions in these class - the MeasureItem and the DrawItem function. You can do this like that (see comments): void CTransparentListBox::DrawItem(LPDRAWITEMSTRUCT lpDrawItemStruct) { static CString strText; // no need to reallocate each time CDC dcContext; CRect rItemRect(lpDrawItemStruct->rcItem); COLORREF crText = 0x00000000; // white CBrush brHighlightBrush; CB...

Referencing a control on subform 1 from subform 3 causes access to crash
Hi Folks Using Access 2003 SP3, I have a main form with 3 linked subforms to a propertyID and fundID on the main form. On the AfterUpdate Event of a field on subform 3 I would like the value to be transferred to a control field on subform 1. This is the syntax I have on subform 3 Forms!FrmFOA!FrmGrantSubFrm.Form![CertDate] = Me!CertificateDate frmgrantSubFrm is subform 1 Every time I enter data in the field to trigger the AfterUpdate event Access crashes. I have gone through the form to see if there is any other event happening at the same time but this is the only pie...

Populating a subform and saving to the underlying tables
Populating a subform and saving to the underlying tables I have created a database of three tables Client, Project, Participant, TestName and Scores. I have enabled referential integrity in the relationships. Client may have many Projects and a project may have many Participants. I have three combo box controls the value selected in the Client cbo is used in the row source query to populate the Projects cbo and that value is used in the row source query to populate the Participants cbo. When I select a participant I want to populate a "grid" with the participant'...

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...

Subform view
Hi all, I have one subform in a main form which is working fine with the exception of how it looks whenever I open the main form. Whenever I open my main form, the view of the subform is always in the middle of the form. I need to scroll up to see the top of my subform. The subform does contain a lot of fields so I understand my users will need to use scroll bars. But is there anyway to start the view at the top of the subform? It is highly annoying to have to scroll up every time the record changes in order to start at the top of the subform. Any insight is greatly appreciated. Thank...

populate a listbox with values from a range in a closed workbook on a server
How would I populate a listbox with details from a range from a close workbook on a server -- Message posted from http://www.ExcelForum.com Either open it and get the data, or use ADO and get the recordset into an array. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "dovrox >" <<dovrox.1654m4@excelforum-nospam.com> wrote in message news:dovrox.1654m4@excelforum-nospam.com... > How would I populate a listbox with details from a range from a closed > workbook on a server. ...

loop through field names, then use to loop through records to populate grid
Sorry, I know this has been done 1000 times. The problem is i need to loop though a recordset and I dont know the field names, so they have to be looked up. When i pass them to get the values, it passes as a literal. fcount = rs.Fields.Count 'Debug.Print fcount For i = 0 To fcount - 1 StrText = StrText & "rs![" & rs.Fields(i).Name & "] & " ++++++ THIS PART WORKS Debug.Print StrText ctGrid0.AddColumn rs.Fields(i).Name, rs.Fields(i).Size ctGrid0.ColumnSortable(i) = -1 If fcount - i = 1 Then D...

Determine subform total before code runs
I asked this question before, but it was in another thread. The only person replying in that thread did not post in response to my follow-up question after several days, so I am posting again. I have a typical Purchase Order database (Access 2003) in which the main PO information is in the PO record (and on the main PO form) and the line items are in a related table (and appear on a continuous subform). There is more, such as a Products table, but it is not relavant to the question at hand. Every PO is approved by several departments. When the line items total for a single purchase ...