Form, Query, and Updating Records

I have an employee profile form that I would like to filter by what
location the employee works out of.

The location is already determined based on a login I have in the
beginning. The location value is stored in a Query (call it query1).

When I get the records filtered to what I want, I still want to be
able to edit/update them.

Is this possible?

I have played around with the Adavanced Filter but haven't been
successful because when I filter the correct field in the employee
profile form, and put my EXPRESSION (I think this is part of the
problem) of what I want it filted by ([Query1]![Location]) it will ask
my, when I run the query, to give a value for Query1!Location. When I
do, it works. But, I can't have that popping up every time.

When I edit the filter and save it as a query, I then add the Query1
as a table. Then run the query, and it doesn't ask me for a value.
But, when I then make a form based on this new query (Query2) then it
won't let me update/edit the records.

Please help.

0
pcstechnical
9/24/2007 7:04:31 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
561 Views

Similar Articles

[PageSpeed] 8

Hi

You say that the location is stored in a query.  Queries don't store 
anything so I'll assume the location is stored in a table, say, tblLocation, 
with a single field called Location.

Create a query with all the info you want in your form and also join to 
tblLocation.
The query SQL should look something like...

Select tblEmployee.myfield1, tblEmployee.myfield2, tblEmployee.myfield3
From tblEmployee inner join tblLocation
On tblEmployee.Location = tblLocation.Location

Then use this query as the source of your form.

Note that the above SQL is a guide only. Your actual query may have more 
than 2 tables and you'll probably be using something like LocationID to join 
instead of Location.

If you still can't edit the data in your form there may be other reasons why 
the query isn't updateable - look up "updateable query" in the help and post 
the query SQL back here.

An alternative approach is to avoid joining to the location table at all.  
Just create a query with all info required and in the criteria for the 
Location field put the following

= dfirst("Location", "tblLocation")

This function will find the first value of the field Location in the table 
tblLocation.
Replace with your own field and table names.  You can also put a query name 
instead of a table so you could use your Query1 if necessary.

hth

Andy Hull


"pcstechnical.sfhs@gmail.com" wrote:

> I have an employee profile form that I would like to filter by what
> location the employee works out of.
> 
> The location is already determined based on a login I have in the
> beginning. The location value is stored in a Query (call it query1).
> 
> When I get the records filtered to what I want, I still want to be
> able to edit/update them.
> 
> Is this possible?
> 
> I have played around with the Adavanced Filter but haven't been
> successful because when I filter the correct field in the employee
> profile form, and put my EXPRESSION (I think this is part of the
> problem) of what I want it filted by ([Query1]![Location]) it will ask
> my, when I run the query, to give a value for Query1!Location. When I
> do, it works. But, I can't have that popping up every time.
> 
> When I edit the filter and save it as a query, I then add the Query1
> as a table. Then run the query, and it doesn't ask me for a value.
> But, when I then make a form based on this new query (Query2) then it
> won't let me update/edit the records.
> 
> Please help.
> 
> 
0
Utf
9/25/2007 11:30:02 AM
On Sep 25, 6:30 am, Andy Hull <AndyH...@discussions.microsoft.com>
wrote:
> Hi
>
> You say that the location is stored in a query.  Queries don't store
> anything so I'll assume the location is stored in a table, say, tblLocation,
> with a single field called Location.
>
> Create a query with all the info you want in your form and also join to
> tblLocation.
> The query SQL should look something like...
>
> Select tblEmployee.myfield1, tblEmployee.myfield2, tblEmployee.myfield3
> From tblEmployee inner join tblLocation
> On tblEmployee.Location = tblLocation.Location
>
> Then use this query as the source of your form.
>
> Note that the above SQL is a guide only. Your actual query may have more
> than 2 tables and you'll probably be using something like LocationID to join
> instead of Location.
>
> If you still can't edit the data in your form there may be other reasons why
> the query isn't updateable - look up "updateable query" in the help and post
> the query SQL back here.
>
> An alternative approach is to avoid joining to the location table at all.  
> Just create a query with all info required and in the criteria for the
> Location field put the following
>
> = dfirst("Location", "tblLocation")
>
> This function will find the first value of the field Location in the table
> tblLocation.
> Replace with your own field and table names.  You can also put a query name
> instead of a table so you could use your Query1 if necessary.
>
> hth
>
> Andy Hull
>
>
>
> "pcstechnical.s...@gmail.com" wrote:
> > I have an employee profile form that I would like to filter by what
> > location the employee works out of.
>
> > The location is already determined based on a login I have in the
> > beginning. The location value is stored in a Query (call it query1).
>
> > When I get the records filtered to what I want, I still want to be
> > able to edit/update them.
>
> > Is this possible?
>
> > I have played around with the Adavanced Filter but haven't been
> > successful because when I filter the correct field in the employee
> > profile form, and put my EXPRESSION (I think this is part of the
> > problem) of what I want it filted by ([Query1]![Location]) it will ask
> > my, when I run the query, to give a value for Query1!Location. When I
> > do, it works. But, I can't have that popping up every time.
>
> > When I edit the filter and save it as a query, I then add the Query1
> > as a table. Then run the query, and it doesn't ask me for a value.
> > But, when I then make a form based on this new query (Query2) then it
> > won't let me update/edit the records.
>
> > Please help.- Hide quoted text -
>
> - Show quoted text -

Well, stored may be a deceptive word.  Supervisor logs in, and I have
a query that takes the their ID number and uses the Supervisor profile
table to figure out what location they belong to. Then the query only
shows the location name. So It's a column, one record query that has a
location name. I wanted to use that location name to filter all the
data in my forms.

I'm not very proficiant in VBA or SQL, but I can try. Any other
suggestions based on this new info?

0
pcstechnical
9/25/2007 1:11:12 PM
On Sep 25, 6:30 am, Andy Hull <AndyH...@discussions.microsoft.com>
wrote:
> Hi
>
> You say that the location is stored in a query.  Queries don't store
> anything so I'll assume the location is stored in a table, say, tblLocation,
> with a single field called Location.
>
> Create a query with all the info you want in your form and also join to
> tblLocation.
> The query SQL should look something like...
>
> Select tblEmployee.myfield1, tblEmployee.myfield2, tblEmployee.myfield3
> From tblEmployee inner join tblLocation
> On tblEmployee.Location = tblLocation.Location
>
> Then use this query as the source of your form.
>
> Note that the above SQL is a guide only. Your actual query may have more
> than 2 tables and you'll probably be using something like LocationID to join
> instead of Location.
>
> If you still can't edit the data in your form there may be other reasons why
> the query isn't updateable - look up "updateable query" in the help and post
> the query SQL back here.
>
> An alternative approach is to avoid joining to the location table at all.  
> Just create a query with all info required and in the criteria for the
> Location field put the following
>
> = dfirst("Location", "tblLocation")
>
> This function will find the first value of the field Location in the table
> tblLocation.
> Replace with your own field and table names.  You can also put a query name
> instead of a table so you could use your Query1 if necessary.
>
> hth
>
> Andy Hull
>
>
>
> "pcstechnical.s...@gmail.com" wrote:
> > I have an employee profile form that I would like to filter by what
> > location the employee works out of.
>
> > The location is already determined based on a login I have in the
> > beginning. The location value is stored in a Query (call it query1).
>
> > When I get the records filtered to what I want, I still want to be
> > able to edit/update them.
>
> > Is this possible?
>
> > I have played around with the Adavanced Filter but haven't been
> > successful because when I filter the correct field in the employee
> > profile form, and put my EXPRESSION (I think this is part of the
> > problem) of what I want it filted by ([Query1]![Location]) it will ask
> > my, when I run the query, to give a value for Query1!Location. When I
> > do, it works. But, I can't have that popping up every time.
>
> > When I edit the filter and save it as a query, I then add the Query1
> > as a table. Then run the query, and it doesn't ask me for a value.
> > But, when I then make a form based on this new query (Query2) then it
> > won't let me update/edit the records.
>
> > Please help.- Hide quoted text -
>
> - Show quoted text -

This also might help: When they log in, they select their name from a
combo box and punch in their password. The combo box binds their
employee id number to a table. Is there any way I can bind two columns
to two different tables?

0
pcstechnical
9/25/2007 1:51:40 PM
Hi again

Ok, if I understand, you already have a query set up that will tell you the 
location for the user currently logged in.  Good - this is exactly what we 
need.

You also need a query to base your form on - which you also have.

The final step is to restrict the form's query using the location query.
2 possible options are:

1) Go into the form's query and add the location query as a table joining on 
the location field.

2) Go into the form's query and put the following as the criteria for the 
location field
= dlookup("Location", "Query1")

where Query1 is the name of the location query (and Location is its field 
name).


If I haven't explained very clearly then post the SQL of your form's query 
and the location query and I'll try to post back the SQL you need.

hth

Andy Hull


"pcstechnical.sfhs@gmail.com" wrote:

> On Sep 25, 6:30 am, Andy Hull <AndyH...@discussions.microsoft.com>
> wrote:
> > Hi
> >
> > You say that the location is stored in a query.  Queries don't store
> > anything so I'll assume the location is stored in a table, say, tblLocation,
> > with a single field called Location.
> >
> > Create a query with all the info you want in your form and also join to
> > tblLocation.
> > The query SQL should look something like...
> >
> > Select tblEmployee.myfield1, tblEmployee.myfield2, tblEmployee.myfield3
> > From tblEmployee inner join tblLocation
> > On tblEmployee.Location = tblLocation.Location
> >
> > Then use this query as the source of your form.
> >
> > Note that the above SQL is a guide only. Your actual query may have more
> > than 2 tables and you'll probably be using something like LocationID to join
> > instead of Location.
> >
> > If you still can't edit the data in your form there may be other reasons why
> > the query isn't updateable - look up "updateable query" in the help and post
> > the query SQL back here.
> >
> > An alternative approach is to avoid joining to the location table at all.  
> > Just create a query with all info required and in the criteria for the
> > Location field put the following
> >
> > = dfirst("Location", "tblLocation")
> >
> > This function will find the first value of the field Location in the table
> > tblLocation.
> > Replace with your own field and table names.  You can also put a query name
> > instead of a table so you could use your Query1 if necessary.
> >
> > hth
> >
> > Andy Hull
> >
> >
> >
> > "pcstechnical.s...@gmail.com" wrote:
> > > I have an employee profile form that I would like to filter by what
> > > location the employee works out of.
> >
> > > The location is already determined based on a login I have in the
> > > beginning. The location value is stored in a Query (call it query1).
> >
> > > When I get the records filtered to what I want, I still want to be
> > > able to edit/update them.
> >
> > > Is this possible?
> >
> > > I have played around with the Adavanced Filter but haven't been
> > > successful because when I filter the correct field in the employee
> > > profile form, and put my EXPRESSION (I think this is part of the
> > > problem) of what I want it filted by ([Query1]![Location]) it will ask
> > > my, when I run the query, to give a value for Query1!Location. When I
> > > do, it works. But, I can't have that popping up every time.
> >
> > > When I edit the filter and save it as a query, I then add the Query1
> > > as a table. Then run the query, and it doesn't ask me for a value.
> > > But, when I then make a form based on this new query (Query2) then it
> > > won't let me update/edit the records.
> >
> > > Please help.- Hide quoted text -
> >
> > - Show quoted text -
> 
> This also might help: When they log in, they select their name from a
> combo box and punch in their password. The combo box binds their
> employee id number to a table. Is there any way I can bind two columns
> to two different tables?
> 
> 
0
Utf
9/26/2007 10:14:01 AM
Reply:

Similar Artilces:

Update tab function
I have the list of tasks in one sheet (All) but I would like to have the list of the ones I mark as "yes" in column F in sheet1 in tab named "recent" Hi Try: =IF(ROW($A2)<=COUNTIF(All!$F:$F,"?*"),INDEX(All!A:A,SMALL(IF(All!$F$1:$F$100<>"",ROW($A$1:$A$100)),ROW($A2))),"") -- it is the array formula - press:Ctrl+Shift+Enter-- Eva Click yes if I helped "Ivone" wrote: > I have the list of tasks in one sheet (All) but I would like to have the list > of the ones I mark as "yes" in column F in shee...

Reservation Form
I have an active website for my business. I built it in Frontpage 2002. It no longer will send email through the settings I used ( Reservation form) and I cannot get it to take any of the settings I give it. I do not think that my knowledge is sufisticated enough. And, I don't think that I am entering the information completely enough. I am using a simple form with a single enter button. Can you help? May be possible. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression Web "FrontPage 2002 on windows XP" <FrontPage200...

Auto populate a form form an open form
Hi I have a main form with customer names and addresses and would like to add a control button on this form which opens up another form which is used for recording telephone calls on. What I have been trying to do is auto populate this call log form with information from the main form ie name and address? Any thoughts Thanks Chris Chris, Please don't re-post the same question twice. You have to be patient and wait for a reply. Responders are volunteers, and you may not get an answer right away. Posting twice causes some people to respond to the first post, while others are ...

What is the purpose of 'Dummy' window in the Command forms
I am wondering why GP has Dummy window with title "~internal~" for the Command forms. For example Command_Financial form has Dummy window Do I need to create Dummy window for my Command forms. If so why I need it. Any comments appreciated!!! Hi Mandeep, The Dummy window is there as a placeholder. It really doesn't do anything, but should be there in order for the form to work properly since your form needs to be open for the commands to be available. The title ~internal~ just prevents this window from showing up in the Security assignment windows. Make sure that...

Crosstab query 02-07-08
I have a base query (qselDEQExport2_Test) with the following SQL statement SELECT qselChemDetail_W_Const.autWellChemDetailID, qselChemDetail_W_Const.autWellChemID, qselChemDetail_W_Const.autConstID, qselChemDetail_W_Const.txtConstituent, IIf([ysnNotDetected]=-1,"ND",[sglValue]) AS [Value], qselChemDetail_W_Const.sglValue, qselChemDetail_W_Const.autUnitID, qselChemDetail_W_Const.ysnNotDetected FROM qselChemDetail_W_Const; All basic stuff, except for the one Iif statement that creates a column called "Value" When I try to build the crosstab query, it will not let me...

Different graphic for each record in mail merge document
I have 2000 lines in excel which is used to make a merge doc. In the mail merge document, I want to have a graphic associated with each record. How can I modify the merge to make in sort that the graphic will change for each excel line (simultaneous during the merge process). What I have done is the following: 1- I have created a chart for each record in excel. 2- I have added a column to the data table and enter the name of the appropriate chart for each record. 3- I have linked the graphic of the line 2 (the first one) in the mail merge document but doing a paste with link. So, when I...

i have problem code 646 windows update encountered an unknown erro
how to fix it ? Hello komang, > how to fix it ? Reading other posts, and searching Google, Yahoo, Bing etc. etc. When requesting help in a newsgroup or a forum, please state your full Windows version, edition, and service pack level, IE version? and perhaps what other antivirus Security suite and firewall or antimalware software you may have installed. Please do so next time. <http://support.microsoft.com/kb/555375> -=- Take a look at this post and see if it helps- <http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&lang=en&...

form pozition on screen
I would like to find form position on screen. It's similar to DoCmd.movesize, but I want discover "parameters' for form position (left corner, top corner, etc) Are you saying that you want to find out what the current position IS? If so, you can use something like this: MyFormLeftVariable = Me.Left MyFormTopVariable = Me.Top MsgBox "My Form is at " & MyFormLeftVariable & VbCrLf & MyFormTopVariable Or however you want to use it. -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com ___________...

copy properties Under forms
Hi, I'm wondering if someone can help me with something on copying properties. I've checked the access help but can't find anything there. This is what i want to do. I'm copying text boxes, labels that has caption, name and other information in the properties that i want to keep when i copy the information to another tab on a form. Is there a way i can copy the property information without doing alot of manual work by going into each text or label box. I hope this makes sense. thanks for you help in advance. Keith When you copy a control, everything about the cop...

Unique Records #2
Hi Hoping someone might be able to help. I currently use the formula =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1) from the Contextures website to count unique records in data sets, where a record is repeated in two or more rows. I create an additional column as required, and use this field as my count data. Works a treat. However I now have a large dataset (400000 records) for which this is incredibly slow and painful. Is it possible to create a macro to do the same thing, and if so how do I do this and will this speed things up? Thanks in advance. Cheers, Anthony =SUMPRODUCT(-...

Try on this important update that comes from the Microsoft
--ytijfeavy Content-Type: multipart/related; boundary="zkbviobxcb"; type="multipart/alternative" --zkbviobxcb Content-Type: multipart/alternative; boundary="ahhkscabjxsk" --ahhkscabjxsk Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Consumer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to help p...

Cannot open the Insert object (Pdf file) form Excel 2000(9.0.8968 SP-3)
Dear All, I am no idea why I cannot open the embedded object (Pdf file) inside Excel file which using Windows ME. Once clicked the object icons, then it shows the error message likes " the embedded object cannot be opened by the source application" I used the Adobe reader 6.01 as the source application. Actually, I searched the related forum from the Internet. However, I cannot solve the problem. Anyone helps? Thanks tlee Hi, I can't test 2000 in ME, but it works fine in 2003 under XP. -- If this helps, please click the Yes button. Cheers, Shane Devenshire &q...

Form class module for non-existent form
Hi, After copying a form with a class module, something went wrong. I now have a class module for the new form, but the form no longer exists. I am unable to remove the in-limbo class module. I want to use that name for my form, which is not possible as long as that class module is in the way. How do I get rid of the in-limbo class module? I've tried repairing tha DB as well as "msaccess /decompile" but it hasn't helped. Anything else? Thanks, Kjell Kjell, A class module is not part of a form, but a separate module. It sounds like your 'in-limbo' module is...

Setting the Windows Cursor during Form Processing
hiya gurus I would like to force the mouse cursor to immediately become an hourglass when some long processing/quering commences on a form. it appears that it takes some time for Access to show the hourglass and usually only after the user clicks the form a couple of times because there appears to be nothing happening... Is there an easy way to do this from within Access.. I have done it in VB6 using Me.MousePointer = vbHourglass but this does nto work in Access.. any suggestions? cheers Jeff mmmmm Screen.MousePointer = 11 guess this does it? "jeff" <jeff@work...

TOURNAMENT FORMS
Need tournament draw sheet single elimination template. You may find some old NCAA basketball tournament forms out on the web. Try http://google.com HHSTENNIS wrote: > > Need tournament draw sheet single elimination template. -- Dave Peterson ec35720@msn.com ...

Query too Complex...
words I have seen way too much of today! And if it is not "too compex" then I get a message saying "insufficient resources". I have spent the day trying to break up boards into smaller sections and trying to find other ways to get the same answer. Eventually I have to bring the seperated boards back together again, so the end result is still the same: Query is too complex Does anyone have a suggestion that I could try to get around these frustrating comments? Should I be using subqueries? - would that help simplify things in the FROM clause? Tara Without seeing ...

Compare update through passport and direct download
I like the update feature that uses my passport in that it gets all accounts at once but... I really don't like having my financial data on a microsoft computer and the service seems spotty, at least during the last 3 weeks that I have been using it. Would I be better off just downloading account data from my bank's web site in money format and importing it into money? Roger "Roger" <x@y> wrote in message news:u7MIxjOiGHA.4776@TK2MSFTNGP05.phx.gbl... > > Would I be better off just downloading account data from my bank's web > site in money form...

Position 2nd Form according to 1st Form
I have 2 Forms. When the 1st Form is openned and want I open the 2nd Form, I want it to position itself about 15cm form the Left and 20cm from the top of the 1st Form. I am using Access 2003, Thanks. "AY" <AY@discussions.microsoft.com> wrote in message news:CFA3A4B5-36D9-4A20-AB57-601290155335@microsoft.com... >I have 2 Forms. When the 1st Form is openned and want I open the 2nd Form, >I > want it to position itself about 15cm form the Left and 20cm from the top > of > the 1st Form. I am using Access 2003, Thanks. You should be able to use Nicole Calinoiu...

select record before and after, then calculate average.
I've got an intersting update query that I can't seem to figure out. What I am trying to do is identify the records where [O3ppm] is zero and update that value by averaging the hour before and the hour after. So for hour 1100, the value should be 0.04 by averaging the [O3ppm] value for the 1000 hour(before) and the 1200 hour(after). Any help is greatly appreciated. Here is a sample of the data: [ID] [DATE] [TIME] [O3ppm] 30898754 7/1/2005 0 0.02 30898755 7/1/2005 100 0.02 30898756 7/1/200...

Trying to delete form border, and it deletes all borders
I made a form with several individual areas that have borders. At the end I decided to try a border around the whole form. Now that I don't like it, I was trying to delete the form border, but it deletes all the borders in the form as well. How can I just delete the border around the form and not the rest of the individual borders? I'm actually on a different computer, because of the printing, will I be able to undo my way out of it maybe, or do I have to be on the computer where I made the form. One more question, will the form retain the undo feature for ever, or will it stop ...

"Operation must use an updateable query" error in IM
Our Client is running GP10 SP1 with all users connecting through Terminal Server to Sql 2005. Receiving error "Operation must use an updateable query" when trying to run an AP Transaction integration that has worked fine until this week. The source files are *txt files and they are not using any Sql scripting in the integration. I get that whenever I have an Access database that has somehow been marked read only or if the user I am logged in as only has read permissions. Since IM uses an Access database internally, I would suggest checking that the user who's logged i...

Run Macro only if certain form is open
I have programed a maco to open one of various forms depending on criteria on an open form. Is there a way to run a "follow up Macro" based on which form opens? In other words can I run a macro based on wheather a certain form is active? -- Thanks, Chuck Take a look at the RunMacro Method of the DoCmd object. You can have it as part of the OnLoad of the form in question. Chuck wrote: >I have programed a maco to open one of various forms depending on criteria on >an open form. Is there a way to run a "follow up Macro" based on which form >opens? In other w...

Forms and Input Data
I created a report form on Sheet 1 and a set of input data on subsequen Sheets 2, 3, ... link to the report. The report is to show informatio on either of the data I select. Are there simple steps for me to pick either set of data I want t display on the report? I've thought of a pull-down menu so that if select "1", the report will show data set "1", and so on. Yet I don' know how to do it. I tried the "replace" command by highlighting al the cell in the Report sheet then type "2" in place of "1" to replac the data set then hitting...

Modal form opens on startup
Evenin' - I hope this doesn't come across as stupid, but I'm almost completely unfamiliar with Access. A client of mine has an Access 2000 database containing some business information, and he'd like to have me import it into Quickbooks. The problem I have is that I cannot get to the information to export it from Access. When the file is opened, I get a (I presume) modal form, configured as a 'splash screen' type of thing. Has my client's logo, and the name of the fellow who wrote the Access app. Click that, and it closes, then opens another form for...

how to solve "Non-modal forms cannot be displayed in this host application"
Hi, I am trying to make a modalless form in VB6 that is compiled as an ActiveX DLL. When i use VC6 to instance the activex DLL, my problem appears that "406 Non-modal forms cannot be displayed in this host application from an ActiveX DLL, ActiveX control or Property Page." I have my form in modalless style in VB6 and they should be instanced in VC. Does anyone know how to get this to work ? Many Thanks, Xiao http://support.microsoft.com/default.aspx?kbid=176468 -- Regards, Nish [VC++ MVP] http://www.voidnish.com http://blog.voidnish.com "arbidol" <arbidol@tom...