Solved

Changing the recordsource and passing parameters

Posted on 1998-05-11
2
357 Views
Last Modified: 2008-02-26
I have a form that has it's recordsource set to a query which includes the data from two tables.  The first table is a master table and the second is a detail table.  I am using an unbound form to ask the user which master record they want.  The form has the name & address information from the master table plus a subform for the detail records.  The navigation keys (custom) are in the subform.  All works well for editing and deleting but... when I need to add a detail record I run into trouble.  I know what mode I'm in because I pass openargs to the form.  How can I locate the master record that they want, pass key information to the form and go into add mode?   What about the link fields in the subform?  Can anyone help me?  Does anyone have any samples that do this?  I'm am very frustrated....HELP!
0
Comment
Question by:Accessed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Accepted Solution

by:
Bangerter earned 50 total points
ID: 1967027
create a button on the main form called search. put an unbound text control box next to the button. in the button's 'OnClick' event go to the code builder and put this.

forms![MyMainForm].recordsource = "SELECT DISTINCTROW ....

copy the sql statement of your main form here then at the end of it put in the where clause

where (form![MyMainform]![MyKeyedField] = [MyUnboundField])

here is an example of what the statement it might look like in the buttons 'OnClick' event:

forms![MyMainForm].recordsource = select distincrow COLLECT.InputDate, COLLECT.Lease, COLLECT.Lessee, COLLECT.ILAccount, COLLECT.LesseeID, COLLECT.Address, COLLECT.ILNote, COLLECT.ChgOffAmt, COLLECT.Gross, COLLECT.AucSold
FROM COLLECT
WHERE (COLLECT.Lease=[forms]![fpremaint]![field0])
order by collect.inputdate desc;

i would also put a button titled 'Clear' next to your search button and in its 'OnClick' event put the same thing in the code except leave the 'where' statement out at the end.

you should now be able to search for any record you want and when it comes up you can then click in the subform and add a new record.

understand too that you cant add a record to the subform for a client that does not yet exist in the main form.

let me know if this is not what you wanted and i can you help you further with more detail as to what you want
0
 

Author Comment

by:Accessed
ID: 1967028
Bangerter,

Thank-you for your response - it got me thinking.  My problem - believe it or not - was that I was opening up the form with ACFORMADD.  Duhhhhh
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question