Results 1 to 14 of 14
  1. #1
    noretoc is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    18

    Search subform and goto that record on main form

    Just first want to thank all you guys. I am new to access, and by reading through other posts and making a few myself, I have learned so much.



    What I am trying to do seems simple to me, but may not be so simple. I have a table called recipient, and a form called MAIN that is linked to it. In Main, I have a subform called "feeds" linked to the "feeds" table. There is a field in the feeds table (and showing in the subform) called jobID. I would like to have a button on the main form, that will search through all of the record in the jobid field of the feeds table, find the matching jobID then bring up the record on the main form that is related to the record on the subform that holds the matching data.

    so, example. 10 recipients, each one with five feeds. each feed has a unique jobid. I want to search all the jobID, find the feed that matches, and have the main form go to that recipient. (It dosen't even have to go to the correct feed as each recipient only has a few feeds. I just need the form to go to the right recipient.)

    Thanks for any help.

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I'm trying to get my head around this - what is the subform for? Are you entering data there?

    You have a job id that you want to match when they click the button on the main form - but you don't say what you are matching it to? You have one recipient on the main form, its 5 feeds on the subform all with different job id's. Correct? So what do you want to happen next?

  3. #3
    noretoc is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    18
    The recipients are companies who get electronic feeds. Each one may have three to 5 feeds. The recipients all have information associated with them, which is why the main form is based on the recipient. In addition each feed has a job ID. The people who handle the feeds, know them by the job id, and don't know which company they go to. I mention that, because sometimes they will ask us to change information on a feed, but only have the job id. So, I need to be able to look at each company and see the feeds they get from my end. I also need to see the information about the company unrelated to the feed. That is all in the main part of the form. Then I need to see each feed they have, which is what is in the subform. If the other team though, asks me to change info on a feed, I don't want to go through every company to find the right feed (Remember they only have the feed id, not the company name). I want to enter the job id, and have the main form go directly to the company that the feed is attached to. Now the button can bring up a search box, or I can add a field, to which I can enter the feed number, and click search, and have the main form go to the record. What ever works so that I can enter a jobid, and have the main form go to the company record. Oh, one other thing is that each jobid is unique. if there are say 10 companies, and five feeds each, there would be 50 job ids. Hope this helps.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    OK. Just put a combo box on your main form - say off to the right somewhere - where you can enter the job id. That will have the recipient as well. Then once you have it you can get the record for the main form.

    There are many ways of accomplishing that. For instance, after the user has entered the job id, use a filter to filter the main form for that recipient.

    Access has another way of doing it which I haven't used (I just do everything in VBA!). Google it - finding record from combo box - that would give you other ways to do it.

  5. #5
    noretoc is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    18
    So I put in the combobox, than found this code to move to the record that matches.
    Code:
     Private Sub cboName_AfterUpdate()
    Code:
    Dim varName As Integer
    'Moves Record Selector to record in subform that corresponds to the 'selection in this combo box.
    varName = Me.combo34.Column(0)
    With Me.FeedListsubform.Form
    .RecordsetClone.FindFirst "JobID = " & varName
    If Not .RecordsetClone.NoMatch Then
    .Bookmark = .RecordsetClone.Bookmark
    End With
    End Sun
    But I am strangely getting a run time 6 overflow error? The source of the comno34 control is a number field in a table, so I have no idea what is throwing the overflow error??

    Anyone thing of a cleaner way to do this.
    Combo34 = source = job id of table the feeds are in.
    FeedListsubform = subform with the feed data including JOBID

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Maybe I am misunderstanding. Your combo box for job id is on the main form. You want to display recipient information on the main form. So when you select a job id you want the main form to be populated, not the subform.

    If your subform is linked to the main form it will show all the feed data for the recipient in any case without you having to do anything.

  7. #7
    noretoc is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    18
    That is correct, but the main form does not have the field JobID. I need a way to look through all the jobIDs in the table that the subform is based on. Find the matching one and the record it is on (On the subform table). Then I need to find our which recipient is linked to that record. Then I need the main form to go to that recipient.

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Actually that is what I am proposing - put the job id on the main form as a search/combo box, "Search by Job ID" sort of thing. It will make things a lot easier for you keeping it all in one place instead of having to jump back and forth between main and sub-forms.

  9. #9
    noretoc is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    18
    My Combobox is on the main form, and is showing me all the job ids. It is the afterupdate event to change the record on the main form that I am having trouble with. I looked up a few versions of how to do this, but each one has not worked. The code above is giving me an error. I also found this one:
    Code:
    Private Sub cboShowSup_AfterUpdate()
    strSQL = "SELECT DISTINCTROW Main.* FROM Main " & _
            "INNER JOIN Slave ON " & _
            "Main.FraudRef = Slave.Link_id " & _
            "WHERE Slave.PolicyNumber = " & Me.cboShowSup & ";"
        Me.RecordSource = strSQL
    End Sub
    This worked perfectly in the example database, but the problem here is that he named his table and form the same name, and i don't know enough SQL to know which references of "main" refer to the table, and which to the form. Same with "slave"

  10. #10
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I've changed my mind! Don't make job id a combo box, just make it a regular text box. The reason is that showing them the whole list as you said above is not nice. If they want to search by job id then they should know the exact job id and not select one from a list.

    In the AfterUpdate of the job id:

    (I don't know if the recipient key is a text or a number - here it is a number)

    Dim RecipientX as Long
    RecipientX=Dlookup("RecipientKey","Feeds_tbl","Job ID=" & me!jobidsearch)
    (this gets the recipient from the Feeds table based on the job id that was entered)
    Me.Filter="RecipientKey=" & RecipientX
    Me.FilterOn=True
    Me.Requery

  11. #11
    noretoc is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    18
    hmm, I created a quick test DB, as I am not at work, but I get an error. It seems to be a syntax error. It is SYNTAX Error (Missing Operator) in query Expression 'Job ID = 5", I attached the sample db. Is there something simple I am missing with the code?

  12. #12
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I NEVER put spaces in field names, ever, for just this reason. One can spend hours trying to figure out what is going on and all it is is that Access doesn't like spaces anyway!!

    It is "[Job ID]="

  13. #13
    noretoc is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    18
    Doh! its funny too because on the database I have at work, which I am learning this from I have it as JOBID. I actually just went through and pulled all the spaces out of all of the table (The tables were imported so the fields all had spaces). It took an hour to update everything. I didn't even realize it in this one though. Thank you very much. It is working like a charm now. Can't wait to use it on the main form when I go back in Monday.


    I am slowly learning scripting for access. I am pretty good with VBScript, but I am getting lost on how access formats its object names.

  14. #14
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The first thing you have to learn is the lingo! Access doesn't do "scripting" and objects mean something totally different.

    I don't even understand this statement "but I am getting lost on how access formats its object names"!! LOL

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 11-11-2011, 11:06 AM
  2. Goto record in subform - sometimes
    By RasterImage in forum Forms
    Replies: 6
    Last Post: 09-13-2011, 04:36 PM
  3. Goto a new record in tabbed subform
    By snoopy2003 in forum Programming
    Replies: 3
    Last Post: 03-05-2011, 04:24 PM
  4. Replies: 1
    Last Post: 12-22-2010, 01:28 AM
  5. Replies: 1
    Last Post: 10-13-2010, 12:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums