Sub-form Linked to Main form 1

[fredk]

I have a main form and sub form - the sub form is linked to the main form with two fiels - one is group number, the second is effective date - When a user enters a new record with a group number of a record that is already out there (there can be several records with the same group number)

the sub-form pops up a record with the same group number - Why would it do that if the effective date is not filled in yet? How would I get the sub-form to link only after the effective date is entered?

Thanks!!!!!!

Fred

 

[FastLearnerIThink]

If you are using bound forms, that is normal. I believe you can only link a subform to a form using 1 field, which appears to be the group number in this case. Of course, if you can link multiple fields, I wouldn't know because I have never seen a need for such.

If you prefer both fields to be considered prior to showing the subform fields, you will need to use an unbound form (no links between main and sub), build the SQL based on the field entries and assign the SQL as the recordsource to the subform. And maybe a command button to execute it.

Dim strSql as string
strSql = "SELECT * FROM tblData

if not isnull(me.txtbox1) or me.txtbox1 <> "" then
strSql = strSql & " WHERE fldField1 = '" & me.txtbox1 & "'"
end if

if not isnull(me.txtbox2) or me.txtbox2 <> "" then
strsql = strsql & " AND fldField2 = '" & me.txtbox2 & "'"
end if

(OPTIONAL)

strSql = strSql & " ORDER BY fldField1"

me.sfmSubForm.Form.RecordSource = strSql



-- Fast Learner, I Think

Here's something to think about. How come you never see a headline like 'Psychic Wins Lottery!'? - Jay Leno

 

[fredk]

Fast learner - thanks for the help - that is what I was afraid of - The only negative that I see with that is when the main form is displayed, it wont show the associated records in the sub-form - is that correct?

Thanks!!!

Fred

 

[FastLearnerIThink]

Correct, but you can set the subform visible property to false to avoid seeing "#Name?" on every text box, and right after you assign the recordsource make it visible.

-- Fast Learner, I Think

Here's something to think about. How come you never see a headline like 'Psychic Wins Lottery!'? - Jay Leno

 

[FastLearnerIThink]

OR you can leave the recordsource as you have it now and using the command button, you can change it to the SQL. Then if you wish, you add another command button to reset to standard recordsource.

-- Fast Learner, I Think

Here's something to think about. How come you never see a headline like 'Psychic Wins Lottery!'? - Jay Leno

 

[fredk]

Thanks for the help!!!!

Fred

 

[FastLearnerIThink]

No problemo and thanks for the star

-- Fast Learner, I Think

Here's something to think about. How come you never see a headline like 'Psychic Wins Lottery!'? - Jay Leno