Results 1 to 7 of 7
  1. #1
    craig1988 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2014
    Posts
    82

    Module to open a form and pass the values given to the form to ranges in excel

    Hi



    The title may not be the best description of what I want so hopefully I can describe it a bit better. See below.

    I would like to use a command button to open a form (no problems). When the form opens the user will have to input two pieces of information. Time and Date.

    The user will then click another command button (lets say SUBMIT) and that will pass the Time (input by the user) to a range of cells in an excel workbook. It will also do the same for the date (input by the user).

    Hopefully somebody can be of assistance.

    Regards
    Craig

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If you mean, enter the time on form and save the record to an excel workbook???
    Why not just enter it via Excel?
    are there more fields?

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you are working in Excel, why involve Access? If readers are going to be helpful, you will have to provide a better description of your database (its purpose, and rules in plain simple English.) What exactly is the issue in "business terms"? You seem more concerned with How to do something, and haven't really described WHAT the issue is.

  4. #4
    craig1988 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2014
    Posts
    82
    Hi Ranman

    Thanks for your ongoing help. I have found another way to get around the problem I was having.

    Her is my next question...

    I currently have a macro with 6 OpenQuery actions. The queries are all append queries and they all have one parameter. This means when I run the macro I have to enter the parameter 6 times.

    The parameter will always be the same for all 6 append queries. Is there a way that I only have to choose the parameter once and it will use it for all 6 append queries.

    Thanks again for your help. You have been great.

    Regards
    Craig

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Put the parameter in a form, in a text box.
    Set the query to look at the form for its param

    form: frmRpt.
    box: txtVal

    the query would then have the param:
    [State] = forms!frmRpt!txtVal


    now no more 6 questions.

  6. #6
    craig1988 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2014
    Posts
    82
    Ranman you are my hero!!

    One last question on this topic... During the append queries I have been appending the date. I used "Date()" in the field.

    I would like to append the words "Previous Summary" to another column is the destination table. What way do I enter this in the field to get it to work?

    See img below

    At the moment, if I run it I am asked for a parameter of previous Summary. I just want access to INSERT the words "Previous Summary" into the SheetSummary Column.

    Click image for larger version. 

Name:	parameter.PNG 
Views:	7 
Size:	5.7 KB 
ID:	18418

  7. #7
    craig1988 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2014
    Posts
    82
    Hi Ranman

    Ignore that last question!! I figured it out and feel silly for asking such a simple question.

    I now have, what I hope, is my last question.

    While running the macro that contains the 6 append queries, I need some vbscript that will ignore the DoCmd.OpenQuery if it does not run correctly and proceed through the rest of the code.

    I am guessing it is an IF statement but I am not sure how to go about it!!

    Have a nice weekend, hopefully I can get this sorted on Monday!!

    Regards
    Craig

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

Similar Threads

  1. Replies: 8
    Last Post: 05-10-2012, 10:57 AM
  2. Pass form values to SQL JOIN operation?
    By jonman03 in forum Queries
    Replies: 3
    Last Post: 03-06-2012, 04:59 PM
  3. Replies: 4
    Last Post: 04-22-2011, 03:08 PM
  4. Pass Variable Values From One Form to Another
    By Nokia N93 in forum Forms
    Replies: 3
    Last Post: 03-07-2011, 11:47 AM
  5. Calling A Module Function To Open A Form
    By orcinus in forum Modules
    Replies: 3
    Last Post: 09-29-2010, 04:43 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