1 to many query vs form

[JethroUK©]

i have a form based on one (client) to many (quals) query

i need to create a new qual (record) for an existing client

in the query view i can merely type a client ID in qualsID in a new record
and i have a new record already populated with client info

if i try the same in the form:

qual = [ID]
DoCmd.GoToRecord , , acNewRec
[ID] = qual

it generates an error - basically asks me to find the client record first

how can the query find it based on the ID - but the form will not - or am i
using the wrong method

 

[Duane Hookom]

Have you tried using a subform for the student quals? Base your main form on
the student table and place the quals in the subform. Set the Link
Master/Child to the StudentID. This is very much like the Orders and
OrderDetails in Northwind.

 

[JethroUK©]

i dont really like/need a subform

i have created a test form based on the exactly the same query and when i
type a client.ID in quals.id of new record it populates the client side
fine - so there is something wrong with my main form - i cant imagine what
it is so i'm going to break it down until i find out

Have you tried using a subform for the student quals? Base your main form on
the student table and place the quals in the subform. Set the Link
Master/Child to the StudentID. This is very much like the Orders and
OrderDetails in Northwind.
--
Duane Hookom
MS Access MVP

i have a form based on one (client) to many (quals) query

i need to create a new qual (record) for an existing client

in the query view i can merely type a client ID in qualsID in a new record
and i have a new record already populated with client info

if i try the same in the form:

qual = [ID]
DoCmd.GoToRecord , , acNewRec
[ID] = qual

it generates an error - basically asks me to find the client record first

how can the query find it based on the ID - but the form will not - or am
i
using the wrong method

 

[JethroUK©]

if i deleted half the fields (didn't matter which half) it worked fine ??? -
it seems to be a problem with the form updating too slowly - so decided to
try the same but using recordsetclone instead of the actual form recordset:

Private Sub cmdNewEnrollment_Click()
Dim qual As Long

qual = [ID]
With RecordsetClone
.AddNew
![ID] = qual
.Update
End With

DoCmd.GoToRecord , , acLast 'goto the new record i just made
End Sub

and it works fine now )

i dont really like/need a subform

i have created a test form based on the exactly the same query and when i
type a client.ID in quals.id of new record it populates the client side
fine - so there is something wrong with my main form - i cant imagine what
it is so i'm going to break it down until i find out

Have you tried using a subform for the student quals? Base your main

form

on
the student table and place the quals in the subform. Set the Link
Master/Child to the StudentID. This is very much like the Orders and
OrderDetails in Northwind.
--
Duane Hookom
MS Access MVP

i have a form based on one (client) to many (quals) query

i need to create a new qual (record) for an existing client

in the query view i can merely type a client ID in qualsID in a new record
and i have a new record already populated with client info

if i try the same in the form:

qual = [ID]
DoCmd.GoToRecord , , acNewRec
[ID] = qual

it generates an error - basically asks me to find the client record first

how can the query find it based on the ID - but the form will not -

or

am