Solved

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

Posted on 2011-03-23
10
325 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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

808 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