?
Solved

access 2007 how to pass a form ID to an update query

Posted on 2011-03-23
10
Medium Priority
?
328 Views
Last Modified: 2012-06-27
Hi,

I am trying to run the following update query when clicking a command button in access 2007 but I cannot seem to pass the [ID] from the current form to the query:

The sql from the query that access has generated is:
UPDATE tblPayroll INNER JOIN tblContractor ON tblPayroll.c_ID = tblContractor.c_ID SET tblPayroll.Locked = -1
WHERE (((tblPayroll.Locked)=0) AND ((tblPayroll.c_ID)=[ID]) AND ((tblPayroll.PaymentDate) Between [Beginning Date] And [Ending Date]));

If i run the query by double clicking it i am prompted for the 'ID' and the 'beginning date' & 'ending date' and the query runs fine (updating only those records that match the [ID].

However, if i run the query by clicking the command button in the contractors form I wish to somehow pass the [ID] in the form automatically to the query so that the user is not prompted to enter the ID themselves.

The command button uses an embedded macro to open the query in datasheet view and edit mode.

Can anyone help please?
0
Comment
Question by:jeff09
[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
  • 5
  • 5
10 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 35203728
On sure method is:

... WHERE tblPayroll.Locked=0 AND tblPayroll.c_I)=fnID() AND Format(tblPayroll.PaymentDate,"yyyymmdd" Between fnBeginningDate() And fnEndingDate);

In module have

Function fnID()
' code will be posted shortly
End Function

Function fnBeginningDate() As String
' code will be posted shortly
End Function

Function fnBeginningDate() As String
' code will be posted shortly
End Function

brb
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 35203756
Function fnID()
  Dim TempVar
  TempVar=Nz(Forms!Form1!txtID,"No Value is detected")
  If TempVar = "No Value is detected" then
     MsgBox TempVar
  Else
    fnID=TempVar
  End If
End Function

Aftr you made this fork, it can be reduced to:

Function fnID()
    fnID=Forms!Form1!txtID
End Function

=========
Function fnBeginningDate() As String
  Dim strTemp As String
  strTemp =Nz(Forms!Form1!txtBeginningDate,"No Value, BeginningDate")
  If strTemp = "No Value, BeginningDate" then
     MsgBox TempVar
  Else
    fnBeginningDate=Format(Forms!Form1!txtBeginningDate,"yyyymmdd")
  End If
End Function

in reduced form after the test:

Function fnBeginningDate() As String
    fnBeginningDate=Format(Forms!Form1!txtBeginningDate,"yyyymmdd")
End Function

do the same for End Date.

Mike


0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 35203763
If you keep geting "No value...", then we have to check speling, etc.

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:jeff09
ID: 35205901
I am receiving a vb compile error  - expected end of statement highlighting "INNER" in the following code:

Private Sub LockPayrollEntry_Click()

UPDATE tblPayroll INNER JOIN tblContractor ON ((tblPayroll.c_ID) = tblContractor.c_ID)) SET tblPayroll.Locked = -1 WHERE (((tblPayroll.Locked)=0) AND ((tblPayroll.c_ID)=fnID()) AND ((tblPayroll.PaymentDate) Between [Beginning Date] And [Ending Date]));

End Sub
0
 

Author Comment

by:jeff09
ID: 35205960
let me expand further.

I am trying to use the command button to open a query (i have attached a screenshot of the query).

In this query i am prompting for the user to enter a beginning date and ending date so i dont think i need any more code for this. But i am also trying to pass the form ID to the query. At the moment the way it works it is prompting the user to enter the ID manually which i do not wish to happen


Untitled.jpg
0
 

Author Comment

by:jeff09
ID: 35206022
I have answered this myself I think.

I have changed the criteria for c_ID in the query from [ME]![ID] to:
[Forms]![frmOpenContractors]![SubDetail].[Form]![ID]

When i click the command button the user is prompted to enter a beginning date and ending date for the query but most ipmortantly the ID is now automatically carried over and the Payroll table Locked Value is only updated where c_ID=ID
0
 

Author Comment

by:jeff09
ID: 35206025
please feel free to review my solution though and if you think there is a more robust or better way to achieve this result then please let me know
0
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 2000 total points
ID: 35208611
A good solution is the one that works. You have managed it by using:

[Forms]![frmOpenContractors]![SubDetail].[Form]![ID]

in the query criteria. Sometimes I use a function call like:

Function fnID()
   fnID=[Forms]![frmOpenContractors]![SubDetail].[Form]![ID]

End Function

Now I plug fnID() as a criteria inside the query. There are a few advantages of using function call. If the query with a function call is not working, it is possible to investigate is in the function code by using MsgBox etc.

Another very good thing about function call is the following situation:

Suppose you have few list boxes on your form like:

lstCity, lstCustomer, lstYear where you may select City and Year not selecting Customer name. Now, you expect your query to ignore Customer name as a criteria because nothing is selected from it.

This complication is could be handled using function call.

To see its power, imagin you have 5 criteria boxes on your form to choose any combination of these 5 criteria to run your query.

1. If you put the criteria directly in the query, you have to build and manage over 100 queries. Where as...

2. If you use function call, all you need is one query with one function call for each criteria.

This sounds like a dream. I have been using this solution since many years. Of course both the function and the way it is presented in the code is a bit different than what I have shown you here. But, if you use a function call, at lease for fnID(), it is a good start for you to get started in you next project, a form with 5 criteria boxes (or as many you desire) as query criteria.

Regards,

Mike

0
 

Author Closing Comment

by:jeff09
ID: 35208649
I have still given you the points as you put me in the right direction with this and have also given me some useful info iro functions.

Thanks
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 35208708
you can send me an email (see my profile) after you post a question with multiple critria as I wrote about is above.

Thank you for the grade and points.

Mike
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

762 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