Access question. SQL Server snobs need not reply

Hi

I seem to be in a Catch 22 here. I Have a Combo box from which the
user selects a description. Then I am using a query to get the rest of
the COURSE details. The current record, from the table ATTEND is
linked to the Table COURSE by the field COURSE_NO. If I try to set Me!
COURSE to rs!COURSE_NO before doing the SaveRecord, it won't let me. I
get error 3331 To make changes to this field, first save the record.
If I try doing the SaveRecord first, as here, I get error 3101 The
Microsoft Jet database engine cannot find a record in the table
'COURSES' with key matching field(s) 'COURSE_NO'. Probably because
that key field has not been set yet. Is there any way around this?

Private Sub COURSE_DESCRIPTION_AfterUpdate()
On Error GoTo CDAU_Err
'fill in other fields from COURSE
Me![PAYROLL NUMBER] = [Forms]![PEOPLE]![PAYROLL]
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Dim db As Database, qd As QueryDef, rs As Recordset, strsql As String
Set db = CurrentDb

Set qd = db.QueryDefs("qryCOURSEDescription")
qd.Parameters("pCOURSE").Value = Me![COURSE DESCRIPTION]
Set rs = qd.OpenRecordset
If rs.EOF And rs.BOF Then
    MsgBox "Error: Record matching that COURSE DESCRIPTION not found."
    GoTo CDAU_Exit
Else
    Me![COURSE] = rs!COURSE_NO
    Me![PROVIDER NUMBER] = rs!PROVNO

End If

Me![DATE].SetFocus
CDAU_Exit:
Exit Sub
CDAU_Err:

MsgBox Err.Number & " " & Err.Description
Resume CDAU_Exit
End Sub

0
Stapes
5/3/2007 9:38:05 AM
access 16762 articles. 3 followers. Follow

5 Replies
599 Views

Similar Articles

[PageSpeed] 40

My question is why are you copying all the data from one table to another? 
If you simply store the course number from your combobox to the recordsource 
of your form, you can get the course description any time by joining it back 
to the course table.

Perhaps you could explain in more detail what you're trying to do.

-- 
--Roger Carlson
  MS Access MVP
  Access Database Samples: www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
  http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


"Stapes" <steve.staple@gmail.com> wrote in message 
news:1178185085.884590.286970@p77g2000hsh.googlegroups.com...
> Hi
>
> I seem to be in a Catch 22 here. I Have a Combo box from which the
> user selects a description. Then I am using a query to get the rest of
> the COURSE details. The current record, from the table ATTEND is
> linked to the Table COURSE by the field COURSE_NO. If I try to set Me!
> COURSE to rs!COURSE_NO before doing the SaveRecord, it won't let me. I
> get error 3331 To make changes to this field, first save the record.
> If I try doing the SaveRecord first, as here, I get error 3101 The
> Microsoft Jet database engine cannot find a record in the table
> 'COURSES' with key matching field(s) 'COURSE_NO'. Probably because
> that key field has not been set yet. Is there any way around this?
>
> Private Sub COURSE_DESCRIPTION_AfterUpdate()
> On Error GoTo CDAU_Err
> 'fill in other fields from COURSE
> Me![PAYROLL NUMBER] = [Forms]![PEOPLE]![PAYROLL]
> DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
> Dim db As Database, qd As QueryDef, rs As Recordset, strsql As String
> Set db = CurrentDb
>
> Set qd = db.QueryDefs("qryCOURSEDescription")
> qd.Parameters("pCOURSE").Value = Me![COURSE DESCRIPTION]
> Set rs = qd.OpenRecordset
> If rs.EOF And rs.BOF Then
>    MsgBox "Error: Record matching that COURSE DESCRIPTION not found."
>    GoTo CDAU_Exit
> Else
>    Me![COURSE] = rs!COURSE_NO
>    Me![PROVIDER NUMBER] = rs!PROVNO
>
> End If
>
> Me![DATE].SetFocus
> CDAU_Exit:
> Exit Sub
> CDAU_Err:
>
> MsgBox Err.Number & " " & Err.Description
> Resume CDAU_Exit
> End Sub
> 


0
Roger
5/3/2007 2:05:46 PM
On 3 May, 15:05, "Roger Carlson" <RogerCarl...@noemail.noemail> wrote:
> My question is why are you copying all the data from one table to another?
> If you simply store the course number from your combobox to the recordsource
> of your form, you can get the course description any time by joining it back
> to the course table.
>
> Perhaps you could explain in more detail what you're trying to do.
>
> --
> --Roger Carlson
>   MS Access MVP
>   Access Database Samples:www.rogersaccesslibrary.com
>   Want answers to your Access questions in your Email?
>   Free subscription:
>  http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
>


I am trying to give the user the option of just typing the Course
Description, and then let the system find the Course No and fill in
the gaps.

0
Stapes
5/3/2007 2:22:26 PM
On 3 May 2007 07:22:26 -0700, Stapes <steve.staple@gmail.com> wrote:

>I am trying to give the user the option of just typing the Course
>Description, and then let the system find the Course No and fill in
>the gaps.

How about having the combo box *DISPLAY* the course description but *STORE*
the course number? Perfectly easy with the combo box wizard; then the user
sees the description, the computer sees the course number, and both are happy.

In any case Roger is perfectly correct: you need not and SHOULD not copy the
other course fields from the courses table into whatever table this Form
represents. You're using a relational database - use it relationally! Store
the course number and use it as a link to *display* the other course
information as needed; don't copy it into a second table.

             John W. Vinson [MVP]
0
John
5/3/2007 3:40:57 PM
and for the record?

I'm not a SQL SNOB
I am just sick and friggin tired of MDB bugs

if MS won't take MDB seriously then they need to kill it
they came to the same conclusion a decade ago-- and that is why DAO and JET
have been depecrated for a decade



"Stapes" <steve.staple@gmail.com> wrote in message
news:1178185085.884590.286970@p77g2000hsh.googlegroups.com...
> Hi
>
> I seem to be in a Catch 22 here. I Have a Combo box from which the
> user selects a description. Then I am using a query to get the rest of
> the COURSE details. The current record, from the table ATTEND is
> linked to the Table COURSE by the field COURSE_NO. If I try to set Me!
> COURSE to rs!COURSE_NO before doing the SaveRecord, it won't let me. I
> get error 3331 To make changes to this field, first save the record.
> If I try doing the SaveRecord first, as here, I get error 3101 The
> Microsoft Jet database engine cannot find a record in the table
> 'COURSES' with key matching field(s) 'COURSE_NO'. Probably because
> that key field has not been set yet. Is there any way around this?
>
> Private Sub COURSE_DESCRIPTION_AfterUpdate()
> On Error GoTo CDAU_Err
> 'fill in other fields from COURSE
> Me![PAYROLL NUMBER] = [Forms]![PEOPLE]![PAYROLL]
> DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
> Dim db As Database, qd As QueryDef, rs As Recordset, strsql As String
> Set db = CurrentDb
>
> Set qd = db.QueryDefs("qryCOURSEDescription")
> qd.Parameters("pCOURSE").Value = Me![COURSE DESCRIPTION]
> Set rs = qd.OpenRecordset
> If rs.EOF And rs.BOF Then
>     MsgBox "Error: Record matching that COURSE DESCRIPTION not found."
>     GoTo CDAU_Exit
> Else
>     Me![COURSE] = rs!COURSE_NO
>     Me![PROVIDER NUMBER] = rs!PROVNO
>
> End If
>
> Me![DATE].SetFocus
> CDAU_Exit:
> Exit Sub
> CDAU_Err:
>
> MsgBox Err.Number & " " & Err.Description
> Resume CDAU_Exit
> End Sub
>


0
Aaron
5/3/2007 11:53:28 PM
On 3 May, 16:40, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On 3 May 2007 07:22:26 -0700,Stapes<steve.sta...@gmail.com> wrote:
>
> >I am trying to give the user the option of just typing the Course
> >Description, and then let the system find the Course No and fill in
> >the gaps.
>
> How about having the combo box *DISPLAY* the course description but *STORE*
> the course number? Perfectly easy with the combo box wizard; then the user
> sees the description, the computer sees the course number, and both are happy.
>
> In any case Roger is perfectly correct: you need not and SHOULD not copy the
> other course fields from the courses table into whatever table this Form
> represents. You're using a relational database - use it relationally! Store
> the course number and use it as a link to *display* the other course
> information as needed; don't copy it into a second table.
>
>              John W. Vinson [MVP]

Thank you - that worked. I get your point about not copying stuff
across. This database was designed by the client, and this is
precisely the sort of tidying up that I am doing.

0
Stapes
5/4/2007 8:45:59 AM
Reply:

Similar Artilces:

asap utilities question
I have Msft. Excel Vsn 2002 and latest vsn. of ASAP Utilities. I used ASAP Utilities to merge single rows of data together so that more than one row will fit on a single line of text. My question now, is, is it possible using ASAP Utilities to separate the data that has been merged back into single rows of data? All information welcomed! Aaron ...

Isinteg Warning Message Question
I ran isinteg -pri -test alltests. I came up with 506 warnings. There are basically 4 types of warnings. They are below. Are these something I need to worry about? What should I do? thanks stewart sschwartz@nal.usda.gov ================================== Warning: MsgFolder 165 (Fid=0001-0000000D1864, Mid=0001- 0000000D559D, Inid=0001-000000F33436): PR_READ_RECEIPT_REQUESTED(0029000B) prop in Messages table and MsgFolder table do not match. Warning: MsgFolder 4 (Fid=0001-0000086DC911, Mid=0001- 00000436F972, Inid=0001-000004A583F1): Error JET_errRecordNotFound seeking to INID for this ...

Business Portal Error-SQL server does not exist or access denied
Hi, We are running business portal 4.0 for one of our customer. It was running correctly, however, they have changed the SQL server port (previously it was set as default 1433). After that the business portal becomes very slow and while creating a new request (purchase requisition) if we open the item pop up; it is showing exception "SQL Server does not exist or access denied...." Can any body tell me how can I provide the new port number to business portal connection to the database. Thanks and Regards, Waliullah, Thanks for using the newsgroups. I have a...

What's happened to the findfirst function in Access 2010?
I have a working Access 2007 Application which is now failing miserably in 2010. It hinges on location the first available working document in a table runing the following VBA code: With Me.Recordset .FindFirst "([fld1] + [fld2]) = 0" If .NoMatch Then .FindLast "([fdl1] + [fld2]) <> 0" Exit Sub End If .... FURTHER PROCESSING ... What must I do to correct this? Go through a record by record search? End With In 2007, this works correctly, stopping at the appropriate record (approc. rec 1385 in the recordset...

help need with VC 6.0 IDE and mfc
Hello, First let me explain the scenario where i m using this requirement. We are Using CustomAppWizard and designing a wizard .One of the wizard pages will Insert Composite controls as many as the user wants . 1.So i should be able to dynamically insert ATL controls without using Insert Control Dailog. 2. can any one tell me how to dynamically create Template file in TEMPLATE folder of resource view . 3. I want to include many files created by templet files and add them to build by editing newproj.inf Is it possible to do this. 4.I would even like to know if i have 2 ifles in my C drive h...

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

How disable postmaster reply in exchange 5.5
I receive mail from virus in my Exchange 5.5 with this format name@mydomain with a virus attached My exchange server reply with postmaster with the original mail attached. I think that this is Mydoom virus. Please help me to disable NDR messages for any e-mail sent to an invalid address. Virus send e-mail with field FROM with addres that want attack and my exchange reply with postmaster with the original mail that contain mydoom virus. Excuse me for my bad english. It is not possible to disable generation of NDR with E55. Why not install antivirus for Exchange? ALEX wrote: > I rece...

Changing SQL Server for CRM 1.2
As my SQL server is currently on its last breath I am in the process of moving our CRM 1.2 databases to a new dedicated serever. I have detatched the databases from the old server, copied the files to the server and attached them. I have then gone into CRM deployment manager and changed to the SQL server to the new server. That all appears to work fine, no error messages. But when users try to access the CRM system they get an error and the CRM server event log fills with error messages. A few are listed below: dmLog: Failed to execute the SQL batch in the file sqlbatch.sql. MSCRM P...

Access 2007 and Vista code problem
I am developing a database on a XP SP2 machine. On a form I have created some buttons with event procedures that run 2 queries and open a form. I sent the db to someone running Access 2007 on a Vista machine and nothing happens when she clicks the buttons and there are no messages. I know nothing about Vista so thanks in advance for any help. Tim Might be a reference problem, have them open the open a code window and click tools references. See what says missing and tell them what should be there, Add it and it should work. Duff "Tim Reid" <TimReid@discussions.micr...

Need Help with Deleting Empty Paragraphs in Word 2003
I have written the code below to delete all empty paragraphs at the end of a document and then place the cursor at the end of the last paragraph. It works fine as a stand alone sub in a new doc, but fails inside the real document that contains other code that manipulates several documents. The failure is that it will delete the last empty para, but then gets stuck looping inside the While...Wend because subsequent .Delete are not happening. So, the question is why would this work in one document, but then fail in another? n = 0 ...

Do not reply to delivery receipts
I need to block delivery receipts. We already know how to do read receipts. It appears it's not that easy when you have Outlook 2003 clients and Exchange 2003 Ent SP1. Any suggestions out there? Thanks for the help. -pepe ...

Relay Servers?
Is there a free or pay service that will alow me to relay my mail through them? I have a online business and use a cable modem from home. My customers that are with AOL do not recieve my Emails. I figure that i can overcome this if I can relay my mail. TIA Josh Please contact your ISP, maybe they can provide one. As far as I know, AOL and Version has this business. Note: Version's relay server may be not compatible with Exchange server "Josh" <jsawyer@insightbb.com> wrote in message news:ePSn%23r2LFHA.1472@TK2MSFTNGP14.phx.gbl... > Is there a free or pay se...

Relay from unstruted server
A certain IIS SMTP server in my company maintained by junior admins got its IP address added to SPAM list at MSN. The junior admins came asking me to allow relay from their insecure server to the Exchange 2000 server that I maintain. My recommendation is that they work with MSN to get their IP address removed from that spam list. Is that likely that MSN/hotmail would remove them from their Spam list ? If so, who would be the contact phone# at MSN to do that ? Do you agree that I should not allow relay from the IIS SMTP to the Exchange server if that IIS box it is not secure and not maintai...

Windows Server 2008 R2 04-09-10
Windows Server 2008 R2 and Windows 7 share the same code? how is that possible when Windows 7 has both 32 bit and 64 bit versions and windows server 2008 r2 is only 64 bit Hello Charle, As Microsoft is going to use only 64bit versions for servers they don't built the 32bit version. Sharing the same code doesn't mean that the server OS use exaclty the same files, there are a lot more and different ones. But the basic code is the same. Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights. ...

Microsoft Exchange Server
I am using Outlook 2003 for my emails from microsoft exchange server. A few days back I neede to reinstall my windows. Everything with the installation and configuration goes fine at my outlook 2003 but I am unable to get back all of my inbox and Sent items from my microsoft exchange server. I can see some of the items in my inbox and some in sent items however a lot of things are still missing. Can anyone help for this problem? Early response is highly appreciated. Thankyou all, Best Regards, Rizwan. Submitted using http://www.outlookforums.com Are all messages shown whe...

Replying to an Outlook e-mail
When replying to e-mail in Outlook, everything works fine. But when I try to reply to an e-mail that has an embedded picture in it, Outlook won't send it. I get the message that the e-mail was sent, but it stays in the outbox and never sends. Any suggestions? -- Jack "Jack Johnson" <hotshotz@comcast.net> wrote in message news:D6C50AC7-EB37-4B54-9611-F9F42F1F1BA0@microsoft.com... > When replying to e-mail in Outlook, everything works fine. But when I try to > reply to an e-mail that has an embedded picture in it, Outlook won't send > it. &...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Dotfuscator question
Hello Everybody !!! When i create programm using C# i must understand that my programm from exe - module can be converted to the source file by Reflector. In Order do not allow do this i must use dotfuscator, but i did not find any free normal dotfuscator. May be someone tell me what can i do cause now as think we have interesting paradox. We 've got free technology but if we want to protect our products we must buy expensive commercial dotfuscators. Alex Dmitriev You should post this message in a newsgroup which discusses managed code. This newsgroup does not have much managed traff...

question on the rules wizard
When clikcing on the Rules Wizard, Outlook locks up - consistently. Has anyone got a remedy for this? Thank you, rich rpage@concerto.com ...

Can't insert Char(1) into SQL Server from Stored Procedure
Hello I am trying to insert a char(1) field into a table from an ASP.Net/C# application. At first I was inserting rows into the table but none of the columns that I was inserting the rows into were char(1) columns, they were mostly int and varchar columns. Then I found out that there was one column in the list that was not supposed to be an int column but instead it was supposed to be one of the char(1) columns into the table. So I replaced the int column in the list with the char(1) column and since the value of the char(1) column resulted from a CheckBox ('Y...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

OE questions
Ok, I have two questions: 1. With Thunderbird I can use this with a hotkey manager to compose a new message with Tbird "D:\Program Files\Mozilla Thunderbird \thunderbird.exe -compose" -- how can I do the same for Outlook Express and Outlook 2003? 2. When I go to load Outlook Express I get this error message, followed by the error message below it: http://i180.photobucket.com/albums/x296/gwar_1/Untitled-3.png how can I fix this and use Outlook Express? Thanks! posted to the outlookexpress newsgroup via crosspost --=20 Peter Please Reply to Newsgroup for the benefit of others Re...

compact database in VBA access 2007
Hello, I have recently upgrade to office 2007 from office 2003. To compact a database from within the database itself, I used the follwing code. Unfortunately it no longer works in access 2007. Is there some similar code that will work? Public Function FncCompactTheCurrentDB() CommandBars("Menu Bar"). _ Controls("Tools"). _ Controls("Database utilities"). _ Controls("Compact and repair database..."). _ accDoDefaultAction End Function Thank You, SL On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft....

question about Time
How to make the time result for example if it�s ( 1:01 ) or higher shows only as ( 1:00 ) and if it�s Lower like ( 0:59 ) or less it will show the same result in this case ( 0:59 ) Any idea & suggestions. Thanks, almufadda@hotmail.com ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Using Ron deBruin's google addin and asking for subject round time, I get http://tinyurl.com/wgua -- Don Guillett SalesAid Software donaldb@281.com "saud" <saud.xgc4...

IE 7 / Win2k3 x64 server things pop-up blocker is on
I have a problem with one server where it thinks the pop-up block is on when it is actually not. I disable the pop-up blocker for all zones in group policy but I continue to receive messages from websites that pop-ups are disabled and that I need to enable them. I don't understand how this can be the case unless IE is doing something its not telling me about. Any ideas? PS IE ESC is disabled on this server. IE ESC was disabled before terminal services was installed. thanks Pete > ...I continue to receive messages from websites that pop-ups are > disabl...