[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

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

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
jeff09
Asked:
jeff09
  • 5
  • 5
1 Solution
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
If you keep geting "No value...", then we have to check speling, etc.

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jeff09Author Commented:
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
 
jeff09Author Commented:
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
 
jeff09Author Commented:
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
 
jeff09Author Commented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
jeff09Author Commented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now