Access question. SQL Server snobs need not reply

[Stapes]

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

 

[Roger Carlson]

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]

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.

 

[John W. Vinson]

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]

 

[Aaron Kempf]

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]

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.