Solved

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

Posted on 2011-03-23
10
322 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 33

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 33

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 33

Expert Comment

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

0
 

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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 33

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 33

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now