Solved

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

Posted on 2011-03-23
10
323 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

932 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

10 Experts available now in Live!

Get 1:1 Help Now