Subform Deleting Problem

I've got a continuos sub form with a recordsource that's listed below. The 
subform dispays accounts and their passwords, etc. My problem is because I'm 
using a sql join statement, I can't delete any subform records from the 
subform. Normally, I would not use a join statement in a subform, but I was 
forced to do it in order to sort by the user's First and Last name that 
resides in a related users table (The accounts table has a userID field that 
links to users table).

Given that situation, is there any way to delete records from a continuos 
subform if the subform has a recordsource that uses a join like my below 
sql?



RECORDSOURCE:

SELECT a.accID, a.userID, a.accUser, a.accPwd, a.accEmail, u.userLast, 
u.userFirst

FROM accounts AS a INNER JOIN

    users u ON a.userID = u.userID

ORDER BY u.userFirst, u.userLast


0
scott
7/26/2007 6:34:34 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
702 Views

Similar Articles

[PageSpeed] 39

Which table to you want to delete them from (accounts, or users)?

IF you put a command button (cmd_Delete) in the footer of the continuous 
form, you could have code in it that deletes a record from either of the 
tables.  Something like:

Private sub cmd_Delete_Click

   Dim strSQL as string

   strSQL = "DELETE * FROM tbl_Accounts " _
              & "WHERE Account_ID = " & me.txt_Account_ID
   currentdb.execute strsql
  
   me.requery

End Sub

HTH
Dale
-- 
Email address is not valid.
Please reply to newsgroup only.


"scott" wrote:

> I've got a continuos sub form with a recordsource that's listed below. The 
> subform dispays accounts and their passwords, etc. My problem is because I'm 
> using a sql join statement, I can't delete any subform records from the 
> subform. Normally, I would not use a join statement in a subform, but I was 
> forced to do it in order to sort by the user's First and Last name that 
> resides in a related users table (The accounts table has a userID field that 
> links to users table).
> 
> Given that situation, is there any way to delete records from a continuos 
> subform if the subform has a recordsource that uses a join like my below 
> sql?
> 
> 
> 
> RECORDSOURCE:
> 
> SELECT a.accID, a.userID, a.accUser, a.accPwd, a.accEmail, u.userLast, 
> u.userFirst
> 
> FROM accounts AS a INNER JOIN
> 
>     users u ON a.userID = u.userID
> 
> ORDER BY u.userFirst, u.userLast
> 
> 
> 
0
Utf
7/26/2007 8:24:02 PM
I figured it out. Thanks
"Dale Fye" <dale.fye@nospam.com> wrote in message 
news:CDBCFEBD-5D6C-4C33-971A-2CAF4A1BEB4D@microsoft.com...
> Which table to you want to delete them from (accounts, or users)?
>
> IF you put a command button (cmd_Delete) in the footer of the continuous
> form, you could have code in it that deletes a record from either of the
> tables.  Something like:
>
> Private sub cmd_Delete_Click
>
>   Dim strSQL as string
>
>   strSQL = "DELETE * FROM tbl_Accounts " _
>              & "WHERE Account_ID = " & me.txt_Account_ID
>   currentdb.execute strsql
>
>   me.requery
>
> End Sub
>
> HTH
> Dale
> -- 
> Email address is not valid.
> Please reply to newsgroup only.
>
>
> "scott" wrote:
>
>> I've got a continuos sub form with a recordsource that's listed below. 
>> The
>> subform dispays accounts and their passwords, etc. My problem is because 
>> I'm
>> using a sql join statement, I can't delete any subform records from the
>> subform. Normally, I would not use a join statement in a subform, but I 
>> was
>> forced to do it in order to sort by the user's First and Last name that
>> resides in a related users table (The accounts table has a userID field 
>> that
>> links to users table).
>>
>> Given that situation, is there any way to delete records from a continuos
>> subform if the subform has a recordsource that uses a join like my below
>> sql?
>>
>>
>>
>> RECORDSOURCE:
>>
>> SELECT a.accID, a.userID, a.accUser, a.accPwd, a.accEmail, u.userLast,
>> u.userFirst
>>
>> FROM accounts AS a INNER JOIN
>>
>>     users u ON a.userID = u.userID
>>
>> ORDER BY u.userFirst, u.userLast
>>
>>
>> 


0
scott
7/26/2007 10:13:51 PM
Reply:

Similar Artilces:

product key validation problem after sp2 update
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I work at a university and we have special version with office 2008 for mac with pre installed product key. Until now everything works great, but today, after updating to SP2 I get this message when I try to open any office application: Office for mac has determined that your product key is not valid. Does anyone has a solution (beside removing and installing again without sp2)? Thanks, Avner Traub Same problem here! Harald. Also have the same problem. Please help ! Raluca I'M TOO, SO??? URGENT!!! Same here!...

counter problem(you really want to read this)
i have a workbook that contains two sheets. Sheet one contains a list of names. Each name references an information sheet for that name. I wish to collect data for each name for a specified number of days "x". When that number of days is met, then I wish to click on a button on the toolbar to delete some number "x" of days of data from 1 - "x" including "x". I wish to do this to make the data more current hence meaningful ...

HL-DT-ST DVD+-RW GSA-T2N1 problems
Running Microsoft Media Center Edition SP3 on Dell Inspiron 6400. Poblem - select files from explorer - right click, select Sent To - drive does not appear in window Problem - select files from explorer - drag them to drive D:\ - get error D:\ is not accessible. Incorrect function. What is wrong. Pierre C wrote: > Running Microsoft Media Center Edition SP3 on Dell Inspiron 6400. > > Poblem - select files from explorer - right click, select Sent To > - drive does not appear in window > > Problem - select files from explorer - drag them to drive D...

Cannot delete one message in Windows Mail
A message fron Sen Feinstein to an individual copied to me can not be deleted ? On Wed, 27 Feb 2008 13:43:18 +0100, John Kirby wrote: > A message fron Sen Feinstein to an individual copied to me can not be > deleted ? You have posted this message to the wrong newsgroup. The access in this groups name refers to Microsoft Access, a database program. Please repost to the correct newsgroup for whatever program you are using. I would suggest you include your Windows version number in the message. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail ...

Cascade delete
Hi, I need to find out if the cascade delete option is set on any of the tables in my database. Any help is greatly appreciated. Cheers Mounilk Mounilk (mounilkadakia@hotmail.com) writes: > I need to find out if the cascade delete option is set on any of the > tables in my database. Any help is greatly appreciated. Look up the catalog view sys.foreign_keys in Books Online. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL S...

Problem sharing a workbook
Hi, I've created an Excel workbook (Excel 2003 (11.8120.8122) SP2), placed it on a shared network server, and then selected Tools>Protection>Protect and Share Workbook... with the "Share and track changes" option selected, and password protection set. However, if I attempt to open it when someone else has it open, I get the message stating that the workbook is opened by user so and so and am given the option to open it as Read-Only, Notify, etc. Any idea why this is happening? Thanks, Linda ...

problem installing the windows vista security error code 80070020
it installs hald way then stoped with the error code 80070020 dont no wat to do help plz You receive error 0x80070020 when you use the Windows Update Web site or the Microsoft Update Web site to install updates: http://support.microsoft.com/kb/883825 dariyel wrote: > it installs hald way then stoped with the error code 80070020 dont no wat > to > do help plz dariyel wrote: > it installs hald way then stoped with the error code 80070020 dont no wat to > do help plz Is the keyboard not functioning properly ? Please see: How to ask a question h...

ImageFrame Problem with Empty Reports
I am getting this "type mismatch" error when I try to open a report that has no records. The report normally displays a picture of the client if they have one or more records in the report. If they don't, it should be able to still open the report but just show no records. I have traced the source of the error to the following code in the pageheader of my report. ImageCyclist.picture=[picpath] The [picpath] is a field inserted in the pageheader that contains the pathname or link where the picture of the person is retrieved from. How can I prevent this error from appearing?? ...

Vista and Money 2005 ofx import problem
I recognise some names here from another forum but I thought it may be helpful to canvass some more opinions/expertise. I have Vista Ultimate running and have succesfully loaded Money 2005 UK and updated via the internet. Everything works fine except the bit I need ! I use Barclays Bank and have downloaded .ofx files for several years (Barclays does not support QIF). When trying to do this in Vista I get an error message saying the file is corrupt or not recognised. I have tried all manner of things including testing the ofx file etc and it appears fine and will load into Money run...

I am having problem while installing MS Dynamics in Win XP
I am trying to install Microsoft Dynamics in Win XP, it gives an error saying MS Exchange server 2003 SP1 has to be istalled first I dont have MS exchange server installed in Win XP,what I have is outlook only Can I go ahead and install SP1 for win XP ( THIS IS AN OFFLINE INSTALLTION RIGHT NOW I am not using outlook to get my Email) Let me know how to install the same and the precaution I have to take Most of the errors expected are given for WIN 200 SERVER & 2003 SERVER IN TECHNET HENCE THIS QUESTION TO TYE COMMUNITY Regards Harish We cannot help unless you are m...

blue screen reboot problems
my home prem. vista pc keeps rebooting. Blue screen message is 2 seconds long so i can't read it and several attempts to resolve this by using system restore etc is only making it worse. I've run virus scans in safe mode nothing. My deskstop comes up at startup with "windows installer" 3x then a box says that windows has to close down and then it reboots after 30 seconds or so, no time to try and update to sp1 or find the problem list because it dumps to fast. The blue screen does say TCPIP error or something. Do i have to connect Mircosoft with $$ or is this not f...

Problem receiving some emails
I have not received several emails over the last few days that were sent to me. Address is correct. I have received many emails on those days, but a few never came through. This is very important!!! -- ZC Check to see if those missing emails are accessible on the server by going to your webmail page (using Internet Explorer). If the emails aren't on your provider's server, Windows Mail can't download them. Gary VanderMolen, Microsoft MVP (Mail) "zcovey" wrote in message news:FA5013FF-6E36-47C5-A35B-380509D648F7@microsoft.com... I have not receiv...

Decimal Place Problem via Export
When converting a report into an excel file for email , it somehow shows the 2 decimal places on the excel spreadsheet - even though I had indicated within the report that the Format=Standard Decimal=0 I eventually wanted to create a format like 200,000 but it's giving me 200,000.00 I can manually strip the 2 decimal places in excell but do not want the user to play with this functionality. Could someone assist. Try using the Format() function. Iin your control source of the field on your report, use something like: "=Format([myNumber],######) See Access help file for Forma...

C++ problem
Hi, I need C++ code for Paste comand OpenClipboard GetClipboardData put it where you want it CloseClipboard If you have a more detailed question and useful context for answering it, a better answer can be given. joe On 14 Feb 2007 14:26:54 -0800, petra980@gmail.com wrote: >Hi, >I need C++ code for Paste comand Joseph M. Newcomer [MVP] email: newcomer@flounder.com Web: http://www.flounder.com MVP Tips: http://www.flounder.com/mvp_tips.htm Here is some C++ code for Paste comand http://www.codeproject.com/clipboard/archerclipboard1.asp AliR. <petra980@gmail.com> wrote in ...

Having problems opening pst file
When I try to open a pst file - I get the following message "Properties for this informaton service must be defined prior to use" then I get another pop-up "Errors |\ve been detected in the file C:\WINDOWS\APPLICATION DATA\MICROSOFT\OUTLOOK\outlook.pst. Quit all mail-enabled applications, then use the Inbox Repair Tool" Please help!!! I have important contact information I can't get to. GHR 770-451-8565 x11 "GHR" <anonymous@discussions.microsoft.com> wrote in message news:012d01c3ddf8$5ef50650$a301280a@phx.gbl... > When I try to open a...

X Axis Linear Representation Problem
I have a project involving a hiking trail where I am trying to capture altitude on Y axis versus mile marker location on X axis. I can enter the data in adjoining columns and use the chart wizard but each mile marker value gets positioned along the X axis in uniform increments rather than an actual linear scale representation. In other words, mile markers 3.3 and the next mile marker (3.8) are the same distance apart on the X axis in the graphic representation as is marker 3.8 and the next one (12.5). This distorts the graphic representation of the actual linear distance between the...

Can't delete Contacts in Address Book in Outlook 2000
It might be coincidental, but soon after I installed the Outlook Personal Folders Backup, it seems now I can't delete names from my Contacts and I want to delete names I no longer need to send e-mails to. When I attempt to delete those names, I get this message: Some names could not be deleted from the address list. The action could not be completed. Does anyone know how to get around this? ...

Relationship problems
I have a DB with 6 tables tbl 1 (Person's Info) ClientNum (PK) FName LName + all person info tbl 2 (Employment info) EmpID (PK) ClientNum EmpCodeNum EmpStatus TrainingRelated StartDate EndDate tbl 3 (Employer Codes) EmpCodeNum (PK) EmployerName tbl 4 (Trades) TradeNum (PK) TradeName tbl 5 (Pri-Training) PriTraID (PK) ClientNum TradeNum tbl 6 (Sec-Training) SecTraID (PK) ClientNum TradeNum The problem i'm have is that when i go to create a form to enter all the clients data the de...

Problems with a Folder in Outlook 2000 SR-1
I just started having a problem with MS-Outlook. One of my mail-folders called 'to review' cannot open, every time I open it to see what items are in it, it just makes a weird noise on my hard drive. I did a right click on the Outlook File name and checked the folder size, and it returned with an error message. All other folders (like 30 or so) return a valid folder size. After that I went and ran the Inbox Tool Repair Kit and that returned an error message on that folder as well and told me to run scandisk.....well boys and girls, I don't think Scandisk is on Win2K Pro. Also,...

Send Mail Problem
Hi all One of our user got the below two message from postmaster, i try to disable the rule(list.dsbl.org) from the connection filtering, but the problems still exists. I search my mailserver ip on list.dsbl.org, it listed. To removal need one week. Have any faster way to fix this problems? abc@abc.com on Thu, 7 Dec 2006 08:28:05 +0800 There was a SMTP communication problem with the recipient's email server. Please contact your system administrator. <"Mailserver Host" #5.5.0 smtp;553 sorry, that domain isn't in my list of allowed rcpthosts (#5.7.1)> 1...

Challenging Sort Problem #2
Have a spreadsheet with items. A = Multiple Locations B = item id (in each location) C = Description D = Qty on Hand (some have zero) E thru J = has misc data I am trying to sort by D - Highest qty on hand and bring all locations with it - no matter what qty is showing. A=location B=part number C=part description D=qty on hand spread sheet has 11,000 lines example A B C D 100002 PC12345 tool for PC 2500 100003 PC12345 tool for PC 0 100004 PC12345 tool for PC 400 100005 PC12345 tool for PC 10 100002 PC54321 tool for pc2 30 100003 PC5432...

Error message in subform
This is part of a previous post which I'd like to start a new thread as I fixed half of the problem in the first. I have a form that has a subform, subform1. The subform1 is based on a qry which is based on two tables tblbusiness and tblindividual. On subform1 is a tabcontrol and on one of the tabs is another subform based on a table called tblidcards. I have linked the tabcontrol subform in datasheet format to the subform1 on the primary key, called ID, of the table tblindividual. However when I try to input anything into the subform on the tabcontrol I get an error message The Li...

BCM 2010
Hi, I have serious problem with my BCM 2010 database. I have a BCM 2010 database instaled on one domain user profile (local database without any share). I change a domail and.... forgot to do backup... (I know, I know - idiot) I have 2 database files recovered from old user profile i I don't know what to do with them to mount this datadabe in new profile. I put them into new profile in the same place but I can't see them from BCM database menu or SQL. Anybody know how to mount this database in new user profile? Jack Why wouldn't you ask this in the BCM g...

Partial transpose data problem
I am having trouble figuring out how to transpose data from one sheet to another. My original data [ 'Sheet1' (2 Columns, n number of rows)] in this format: File: file1.dgn Start: 0 Stop: 1 File: file2.dgn Start: 2 Stop: 3 File: file3.dgn Start: 4 Stop: 5 .... And I am trying to transpose it to a second sheet like this 'Sheet2' (3 columns) File Start Stop file1.dgn 0 1 file2.dgn 2 3 file3.dgn 4 5 .... Thus, the actual formulas I need on my 'Sheet2' should be similar to this. =Sheet1!...

Outlook Mail Problem
I can sent emails to my sister in St. Paul, MN to her business address, but when she tries to reply to my messages she is blocked from sending them to me. I live in Tulsa, OK and this problem does NOT happen when we communicate between her home email address and mine, only when she is at work. Any ideas? It would be pure speculation if we tried to guess the cause. We would have to see a verbatim copy of the blocking notice. --=20 Gary VanderMolen, Microsoft MVP (Mail) Microsoft MVP Program: http://mvp.support.microsoft.com "Choppo56" <Choppo56@discussions.micros...