f



Main Form / Sub Form search issues with combo boxes

Hello-

I have a main form "Customers", with two sub forms "Items" & "Transactions"=
..

The forms are all linked through the key fields of ShipDate & AccountNum. T=
ransactions form is linked to Customers, and Items is linked to Transaction=
s.

The account number combo box in the header of the Customer form functions f=
ine. I have combo boxes on the headers of the subforms that work when used =
on the subform alone, but when used as a part of the Customers form, they w=
ill not work correctly. The Transactions subform has an Invoice # combo box=
, and the Items subform has a Sequence # combo box.

I cant get the records in all 3 forms to display the intended record when u=
sing the subform search boxes.

The VBA for the controls is shown below.

Customer Form (Main)

Private Sub cboFindAccount_AfterUpdate()

    ' Find the record that matches the control.
    Dim rs As Object

    Set rs =3D Me.Recordset.Clone
    rs.FindFirst "[AccountNum] =3D " & Str(Me![cboFindAccount])
    Me.Bookmark =3D rs.Bookmark
End Sub

For the Transaction subform

Private Sub cboFindInvoice_AfterUpdate()
' Find the record that matches the control.
    Dim rs As Object

    Set rs =3D Me.Recordset.Clone
    rs.FindFirst "[InvoiceNum] =3D " & Str(Me![cboFindInvoice])
    Me.Bookmark =3D rs.Bookmark
End Sub

For the Items subform

Private Sub cboFindSequence_AfterUpdate()
' Find the record that matches the control.
    Dim rs As Object

    Set rs =3D Me.Recordset.Clone
    rs.FindFirst "[SequenceNum] =3D " & Str(Me![cboFindSequence])
    Me.Bookmark =3D rs.Bookmark
End Sub

Any help would be appreciated.

Thanks
Scott
0
scott.sabo (14)
6/28/2013 1:02:53 AM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

3 Replies
537 Views

Similar Articles

[PageSpeed] 49

On 28/06/2013 02:02:49, Sabosis wrote:
> Hello-
> 
> I have a main form "Customers", with two sub forms "Items" &
> "Transactions" .
> 
> The forms are all linked through the key fields of ShipDate & AccountNum.
> T ransactions form is linked to Customers, and Items is linked to
> Transaction s.
> 
> The account number combo box in the header of the Customer form functions
> f ine. I have combo boxes on the headers of the subforms that work when
> used on the subform alone, but when used as a part of the Customers form,
> they w ill not work correctly. The Transactions subform has an Invoice #
> combo box , and the Items subform has a Sequence # combo box.
> 
> I cant get the records in all 3 forms to display the intended record when
> u sing the subform search boxes.
> 
> The VBA for the controls is shown below.
> 
> Customer Form (Main)
> 
> Private Sub cboFindAccount_AfterUpdate()
> 
> ' Find the record that matches the control.
> Dim rs As Object
> 
> Set rs = Me.Recordset.Clone
> rs.FindFirst "[AccountNum] = " & Str(Me![cboFindAccount])
> Me.Bookmark = rs.Bookmark
> End Sub
> 
> For the Transaction subform
> 
> Private Sub cboFindInvoice_AfterUpdate()
> ' Find the record that matches the control.
> Dim rs As Object
> 
> Set rs = Me.Recordset.Clone
> rs.FindFirst "[InvoiceNum] = " & Str(Me![cboFindInvoice])
> Me.Bookmark = rs.Bookmark
> End Sub
> 
> For the Items subform
> 
> Private Sub cboFindSequence_AfterUpdate()
> ' Find the record that matches the control.
> Dim rs As Object
> 
> Set rs = Me.Recordset.Clone
> rs.FindFirst "[SequenceNum] = " & Str(Me![cboFindSequence])
> Me.Bookmark = rs.Bookmark
> End Sub
> 
> Any help would be appreciated.
> 
> Thanks
> Scott
> 

Are we talking about a straightforward Invoice setup here with a Customer
form, a subform showing invoice headers and a second subform showing invoice
lines (Items)? If so, what is your table structure? What is the SequenceNum
for? Usual way is that you select your customer. That shows all the invoice
headers for that customer probably in reverse date order (most recent at the
top) in your first subform. You then click in one of those invoce headers and
that shows all the invoice lines for the chosen invoice in your second
subform.

Phil
0
phil (1005)
6/28/2013 8:39:29 AM
This has nothing to do with a Customer/Invoice database. It is regarding a =
returned products process within our company. The DB has tables for Custome=
rs, Transactions, and Items. We just want to be able to find open transacti=
ons (those that have not been fully resolved) by searching for a customer n=
umber in the customer table, an invoice number which would be part of the t=
ransaction table, or a sequence number that would be part of the Items tabl=
e. We have customers that must return paperwork to us prior to closing out =
their return, and when we get their paperwork back, these are the pieces of=
 information that we would like to use to find their open transaction in th=
e DB.
0
scott.sabo (14)
6/28/2013 3:24:59 PM
On 28/06/2013 16:24:57, Sabosis wrote:
> This has nothing to do with a Customer/Invoice database. It is regarding a
> returned products process within our company. The DB has tables for
> Custome rs, Transactions, and Items. We just want to be able to find open
> transacti ons (those that have not been fully resolved) by searching for a
> customer n umber in the customer table, an invoice number which would be
> part of the t ransaction table, or a sequence number that would be part of
> the Items tabl e. We have customers that must return paperwork to us prior
> to closing out their return, and when we get their paperwork back, these
> are the pieces of information that we would like to use to find their open
> transaction in th e DB.
> 

So how is the SequenceNum related to the Invoice or customer?
What do you expect to see in the 3 forms / subforms.
I cant get the records in all 3 forms to display the intended record when u
sing the subform search boxes isn't helpful

Phil
0
phil (1005)
6/28/2013 8:24:12 PM
Reply:

Similar Artilces:

Query By Form Using Same Sub Form and Main Form
Having problems displaying query results from combo boxes on a sub form, which is on the same form that is used to select criteria. This has always worked form me when displaying query results on another main and sub form. The requery on the sub form and refresh comands on the main form do not work when the form is first displayed and when the selection criteria is changed. Should I be doing a refresh and then repaint of the sub form. > ' Creates saved query in the database container for the sub form Call BuildSql("") ' The following commented line correctly displays qu...

help, combo/list box on form (sub form)
I have a table: ID_NAME first last another table ID_SUGGESTION suggestion another table ID_BOTH SUGGESTION_ID NAME_ID I don't understand how to use a combo/list box/whatever to enter names and suggestions onto the table with the relationship, the third table with suggestion id and name id. help, pls. there's a 1-many from ID_NAME to NAME_ID, and similarly for suggestion; both with cascade updates and deletes. thanks, "spamspamspam" mapsmapsmaps@mail.com I assume that each person type many suggestions then a simpler way to accomplish this is to eliminate the third ...

Sub-forms of Sub-forms
I'm creating a data-entry form for 3 tables. There is a one-to-many relationship between table1 and table2 and a 2nd one-to-many between table2 and table3. What I would like to do is have the user select from a combo box on the main form a record from Table1. That would filter the subform on the main form to just the records from Table2 with the PK from Table1. I've got everything working fine up to this point, but am a bit confused on where to go next. I need a mechanism for the user to pick one of the records from Table2, and be presented with an entry for for all records in Tab...

focus from main form to sub form
Hi, I have a combobox who's values change the recordsource of the form. Within this form, there is a subform, whos records also need to change pending the value in the combobox. I am able to get the main forms values to change, but not the subform. I've only been using Access 97 for about a month now, so it might be fairly simple. Here is my code: Private Sub combo1_Change() If Me.combo1.Value = "Standard Procedures" Then Me.RecordSource = "tbl_stdProc" ElseIf Me.combo1.Value = "Policy" Then Me.RecordSource = "tbl_policy&q...

Hiding Access Main Form/tables/queries/forms/etc
Thank you in advance for any and all assistance. Is there a way to hide the Main Access window with tables, queries, forms etc programmatically? Michael On Sat, 17 Jan 2004 17:17:16 -0600, "PMBragg" <pmbragg@megavision.com> wrote: >Thank you in advance for any and all assistance. > >Is there a way to hide the Main Access window with tables, queries, forms >etc programmatically? > Yes. Take a look here for some API code that will manipulate the Access window. http://www.mvps.org/access/api/api0019.htm - Jim Another way, with probably less control than the...

Display recordcount of a sub form in a main form
Hi All, I am using A2 for this database. I have a main form with bound fields of FlightNumber and FlightDate I also have a subform. This subform takes details of a passanger along with other details. I also have a textbox called status in subform. When a passanger's details are entered, status value is set to "confirmed", when passanger calls in to reconfirm flight, status value changes to "Reconfirmed", and when passanger calls in to cancell his/hres flight, status value is set to "cancelled". Sometimes, status textbox may also have a value of "Wait...

Problem with Totals on Sub Forms and Main Forms
I have an access data project (MS-SQL Svr 2000 backend and MS-Access 2003 Front End) that has a sub-form based on a query view. On the sub form I have several column's in which I have a running total on the sub-form footer. This part works fine. My problem is that the main form, on which the sub-form resides, I want to display the total from the sub-form total calculation. I have tried referencing the control on the sub form, i.e. My.Somecontrol = Forms![formname].[controlname] but this does not work. How do you make this works. Do I have to do something different because this is ...

Call Function On Main Form to Sub Form
I have a form (frmAppointment) with a subform on it (frmAppointmentsub1). I have placed a command button on the main form which calls the function below from the module. The purpose of this function is to make fields beginning with YY invisble OnClick of the command button on the main form. It works for the fields on the main form but not on the subform. What do I need to add or do to make it work on the subform? Any help would be most appreciated. Thank You TD The command button on the main form event procedure is: Private Sub cmdYY_Click() Dim I As Integer I = glrInvisbl...

Want to search subform but accross all records in main form and return the corresponding record in main form
I'm working on a DB where I have a main form that has customer info and a subform that contains order details related to that specific customer. I have a text box and a command button on the main form that I'd like to be able to use as a search function. What I'm trying to do is search the subform for a purchase order number and have the corresponding customer record brought up in the main form. I'm having some problems because the subform, by nature, is limited to only showing records related to the customer ID that is presented on the main form so I'm not able to searc...

Form
I have a table TBL_NAMES : IDNAME , Name_person , ETC..... (15 different labels) I have a second table TBL_POINTS : IDPOINTS , LINKNAME_PERSON , POINTS_ALGEBRA , POINTS_GYM , etc.... (25 courses) I have made a form where all the names are listed and when i click on a name another form is opened with that persons name on top and a number of labels and textboxes where i put those points in. So far no problem. BUT... i want one record per person. So the next time i click on his name i would like to see those points and be able to change them but i must not be able to create a new record. T...

Form, subform, sub-sub form?
Let' say we have an application for a production facility running 24/7 broken into 3 shifts 6-2,2-10,10-6 each production report date contains 3 shifts as above in each shift there can be from one to several different product runs (the average is about 4) but it could be more or less - it's not defined or definite for many reasons Then, for each run there is a production report of product, good pieces, bad pieces total run time for that product etc. etc. then for each run there is a list for # of rejects and reject code SO I have ProductionRuns with rejects subform 1 to many bu...

Opening form with Value in Combo Box from another form
I have a form where the user selects an item from a list box, and then works on that item. The user chooses an AD, then opens a form to assign departments to the ad. The top of the Depts form has a combo box, to select an ad from the drop down list. I would like the Depts form to open with the Ad selected on the Main form displaying in the combo box, AND any information already added presented to the user. (I am thinking this latter part would be a requery of the combo box or something). My problem is that I can't get the frmDeptsForAd to open with a value in the combo box. Perhaps my...

Limit Sub Form records based on Main Form
I have an option group called Issue_Type on my main form F1_Member_Demographics_Main. When I select one of the 3 options on the main form from the option group Issue_Type I want to limit the sub form F2_Member_Issues records based on the combo box Issue_Code_T1. Option Group Issue_Type on main form: 1=Complaint 2=Grievance 3=Appeal Combo Box on sub form: 1=Complaint 2=Grievance 3=Appeal So if I select Complaint from the option group on the main form I only want to see Complaints on the sub form etc. Thanks in Advance Gary BEETHOVEN wrote: > I have an option group called Issue_Type on ...

Mulitple Main Forms Created by Sub Form Entries
Hi I'm hoping this is a problem just because I'm a beginner at Access, and so someone can help I have created a database of companies, with a subform for contacts. (seperate tables) It worked perfectly until I got more than one contact for a company! If I add a 2nd contact to the subform, when I go back into the database, Ive got 2 records of the main form for the company, with the subform, showing. Its not adding a second record to the company table, that just says I have 2 contacts attached, which is what I want. How do I stop the record, when I view the form , duplicating its...

Open Access Database Form from within Access database
I have included the following code to open an Access database form from within an exsisting Access database. But when I run the code, it appears that it opens the database because in windows explorer I can see the database has the .ldb file extension. So it appears that it's open, but I can't see the database or the form that I've tried to open. What am I doing wrong. The following is the code: 'This is in the declaration part of the module Dim appAccess As Access.Application ' Initialize string to database path. Const strConPathToSamples = "C:\Documents a...

Cannot add or edit to form, nor select from combo box on form
I cannot add or edit to my form, nor select from the combo boxes on my form. Here is the code behind my command button that brings up my form via a query: Private Sub Command108_Click() On Error GoTo Err_Command108_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "frmQuery5" stLinkCriteria = "[CallDate] Between #" & Me.txtBeginQAEvry5th & "# And #" & Me.txtEndQAStopEvry5th & "#" Debug.Print stLinkCriteria DoCmd.OPENFORM stDocName, acNormal, "qryEvery5thQuery", stLinkCriteria,...

Making MS Access Form as a standalone form like in VB
Well, its sames difficult for me; I love access so much and wish to make it perform extra task. I prepared an HTML form but can not connect it to my access table and so I think it is possible to make an access form a standalone form for user input. Can anyone assist. My mail add is dianafather@aim.com. *** Sent via Developersdex http://www.developersdex.com *** Oscar, why don't you just code up a VB form for user input and connect it to an Access backend? To my knowledge, you can't force an Access form to exist outside of Access. Your users would have to open the Access file to ent...

Using a combo box on search form
Would appreciate any help I could get I have an unbound search form with various text boxes on it for searching my database. I would like to have a combo box on the search form where the source is a field in the table I'm trying to search. The form works great except for the combo box. I pull the data from my table (storing the value rather than the ID), but once I hit my search button, no records are found. This field in my table is set to look up a value in a 2nd table. For example, I have a table of the days of the week (tbldays) storing the source, and a second table (tblInvoice) ...

Sorting form records on the unbound column in a combo box on the form
Hi. I want to sort the records on my form (using either a continuous form or a datasheet) by the unbound "description" column in a combo box on the form (or in the datasheet.) Here's a rough text representation of what I'm talking about FORM Item Number Description Category (text box) (text box) (combo box - 2 columns) 1001 Item 1 Col1=100 Col2=CategoryName3 1002 Item 2 Col1=101 Col2=CategoryName4 1003 Item 3 Col1=102 Col2=CategoryN...

Forms and Form
Hi everyone ! 1) An example from help: "Each Form object has a Controls collection, which contains all controls on the form. You can refer to a control on a form either by implicitly or explicitly referring to the Controls collection. Your code will be faster if you refer to the Controls collection implicitly. The following examples show two of the ways you might refer to a control named NewData on the form called OrderForm: ' Implicit reference. Forms!OrderForm!NewData ' Explicit reference. Forms!OrderForm.Controls!NewData The next two examples show how you might refer to a ...

main form with subform and combo boxes
Hello all, First I'd like to apologize...This post was meant to be put in my previous post, but I tried many times without success to reply within my previous post. Now here goes... I have a main form (RD Form) with 4 combo boxes (i.e. cbo1, cbo2, etc) and a subdatasheet (the subform...let's call it subInfo) below the combo boxes on the RD Form. I hope this eliminates any confusion of the interface I am working with. The 4 combo boxes have pre-populated criteria that update each other depending on selections of the combo boxes. For example, user selects the name Dave from cbo1. ...

Forms/Subforms in MS Access Database
Why do my Forms/Subforms in my MS Access Database not appear? All I see is the boxes where they should show?! What did I do wrong? Thanks, John zufie wrote: > Why do my Forms/Subforms in my MS Access Database not appear? > > All I see is the boxes where they should show?! > > What did I do wrong? -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Probably there isn't a name of a form in the sub-form's Source Object property. Also, the Link Child Fields and the Link Master Fields properties may be blank - you must enter the names of the link fields, they hold the fi...

Populate a combo box based on a selection in other combo box on same form
Hi all, In a project I am doing at work, we are producing different types of deliverables. Each deliverable has an occurance in tblDeliverable. Each deliverable has one or more versions, which are stored in tblVersion. Each version belongs to exactly one deliverable. We are doing some test work on the deliverables now, and when something is wrong we record an action (tblAction). Each action is done to exactly one deliverable. I have a form to edit the actions (frmAction). In this form I have a cboDeliverable with a lookup to tblDeliverable. This is how we registratie to what deliverable the action applies. When an action is finished, we would like to store the version of the deliverable where to the action was applied. I am trying to this with a cboVersion in the frmAction. I want the cboVersion to be populated with only versions that belong to the selected deliverable in cboDeliverable, but I can't figure out how to do that. Select Version_ID, Deliverable_ID from tblVersion where Deliverable_ID = Me!cboDeliverable When I open the form I get the message: "Enter parameter Value" "Me!cboDeliverable" I hope someone can help me out here... Thanks! Koen. Sounds like you're looking for dependent combo boxes. I've done this before using: combo box 1 with record type table/query and record source: for example, SELECT [code], [description] FROM [codes Query] WHERE parent_code in (select code from [codes query] where parent_code = 0...

Custom Find/search Form that sets the current record on a main form
I'm trying to create a custom find form. I've looked at some examples and searched the topic, but I'm still failing to get this find form working properly. I have a custom form displaying customers first and last names in a List box. This form works and filters my customers properly. My problem is that I need to be able to double click one of these records in the search form and have it set the current record on my main form. Can anyone provide some examples of how I might do this? Thanks much for the help. fred@besttechsolution.com (fred) wrote in message news:<cc913dbb.0312041928.6d6fca75@posting.google.com>... > I'm trying to create a custom find form. I've looked at some examples > and searched the topic, but I'm still failing to get this find form > working properly. > > I have a custom form displaying customers first and last names in a > List box. This form works and filters my customers properly. My > problem is that I need to be able to double click one of these records > in the search form and have it set the current record on my main form. > > Can anyone provide some examples of how I might do this? > > Thanks much for the help. Hello Fred, Perhaps what you could do is use the Wizard builder on the tool box when selecting a new list box to put on your form. Make the control source the same as what you currently have in the current list box. With the wi...

Web resources about - Main Form / Sub Form search issues with combo boxes - comp.databases.ms-access

SEO, Search Engine Optimisation - Website Optimisation
Hit Search Limited - digital marketing agency specialising in search engine optimisation, affiliates, ROI and pay per click. Online marketing ...

SEO & Search Engine News : SEJ
Latest Search Engine News from the Search Engine Journal.

Search Engine Marketing (SEM), Paid Search Advertising (PPC) & Search Engine Optimization (SEO) - Search ...
The authoritative guide to search engine marketing (SEM), search engine optimization (SEO), paid search advertising (PPC) and social media marketing. ...

Search Newz :: Search Engine News
... To Fight Over Paid Links Google prepared a note about a proposal by French lawmakers and backed by French news publsihers, which want search ...

State of Search - Where Search and Social meet up
The fastest growing Search and Social blog covering strategy developments in search and social media

Google Search Appliance - Wikipedia, the free encyclopedia
its search capabilities include searching web content, other file types (e.g. html, pdf, office documents), databases ( Oracle , MySQL , Microsoft ...

Emergency services frustrated over 'unnecessary' search for students
Qld emergency services were involved in a alrge scale search effort at boys were seen in a stormwater drain.

Child porn: Queensland man facing 46 charges - The Courier-Mail Search Search
A FAR north Queensland man is now accused of almost 50 child pornography offences after being charged with extra matters.

For sale: Pyrmont’s $5 million Terminus Hotel, the pub that time forgot - DailyTelegraph Search Search ...
A PUB which has been shut for 30 years is set for a new lease of life after its mysterious owners decided to sell.

Police called to shootings in Noble Park, Hallam 15 minutes apart - HeraldSun Search Search
POLICE were called to two shootings in Melbourne’s southeast within 15 minutes overnight, making it the fifth shooting in five days.

Resources last updated: 3/9/2016 10:05:52 PM