mainform and subform

[JohnHo]

I have been working with Access for a little while but have never used mainforms/subforms before.

Here is my problem/scenario: I have a main form (frmMain) with a few other forms for data data entry (frmOne, frmTwo, ...). I have the same table (tblCallLog) as the record source for each of these forms. I have used the MainForm and SubForm with the Parent/child linking fields (lngCaseID). I would like to have the data entry in a seires frmOne for first data entry; frmTwo for the next data entry and so on.

When I open frmMain for data entry I create a new record with vba code. When I open frmOne with a command button I would like to show the same record (lngCaseID) as on frmMain but a second set of fields from the underlying table.

I can't make it work. I tried

docmd.openform frmOne,,, stLink 

on click event for the command button -- where stLink is a string with the lngCaseID from the record created on frmMain.

I get the message can't go to the specified record. What else do I need to make this work? any help would be greatly appreciated!!!

/jh

[mshmyob]

I don't know where to start.

First I would guess your design is not normalised since you say you have numerous forms for data entry into one table.

You have a misconception of what and where you use a main form/ subform.

Subforms are usually used between 2 related tables. The main form is usually the One side of the relationship type and the Many side is usually the subform. Therefore when you select a record in the main form, related records to that record from another table are displayed in the subform.

I would recommend reading up on normalization and then use the subforms.

cheers,

[ChipR]

When you work out your design, if you still have this problem it may just be a matter of saving this record before trying to open the other form.

[JohnHo]

Thanks a great deal!!

I do believe the table (tblCallLog) is normailzed as I understand it.

However, the data entry happens in a sequence therefore the series of forms (frmOne, frmTwo). This is purely a function of how data gets entered (app requirements).

If I eliminate the MainForm/SubForm relationship and go with a series of forms I have created the code based on what I think I understand to be legit methods (dangerous I realize) the code looks like this:

 
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 
 
stDocName = "frmTwo" 
 
    stLink1 = "SELECT * FROM tblCallLog " _ 
            & "WHERE lngCaseID = " 
    stLink2 = stLink1 & Chr(39) & Forms!frmOne!tboCaseID & Chr(39) 
 
'open record 
 
    DoCmd.OpenForm stDocName , , , stLink2 

I get the error:

syntax error 'SELECT * FROM tblCallLog WHERE lngCaseID = '123'"

--- it appears as if the SQL statement is a problem. I have tried variations on the statement including chr(34) for double quotes (") around the SQL statement etc. to no avail.

If i go with

 
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 
 
stDocName = "frmTwo" 
 
stLink1 = "SELECT * FROM tblCallLog " _ 
            & "WHERE lngCaseID = " 
    stLink2 = stLink1 & Chr(39) & Forms!frmOne!tboCaseID & Chr(39) 
 
'open record 
 
DoCmd.OpenForm stDocName 
 
Forms.frmTwo.Form.RecordSource = stLink2 
 
 

I get a new record.

Is it wrong-headed to think this could this be solved by just tweaking the SQL statement?

thanks a lot!

/jh

[mshmyob]

Could you show use your table structures please.

Also for you SQl is LongID a text or numeric - if numeric get rid of quotes for the ID.
cheers,

[ChipR]

I'm not sure how you are going to go about creating 1 record across multiple forms. You would have to create the record, then force the user to add data in each of the fields as you progress through the forms. This brings up issues with the user exiting the program after creating the record with a bunch of blank fields. An alternative would be tabs that you progress through, or groups of controls that you show/hide all on one form to enter the record. I suppose you could also have all your fields on the main form, possibly hidden, and code to copy unbound text boxes on individual forms to the appropriate fields on the main form where they would be recorded.

[JohnHo]

Thanks for the reply

I have used this method before in a previous life - representing different fields from an underlying table on sequential forms for data entry purposes. Unfortunately I don't have that app available to me now.

Is a NULL value for a field significant in this case?

The underlying table for frmOne, frmTwo and frmMain is the same tblLog. For each of the numbered forms (frmOne and frmTwo) there are only three fields:

forms.frmOne.tboCaseID
forms.frmOne.tboFieldOne
forms.frmOne.tboFieldTwo
forms.frmOne.tboFieldThree
 

forms.frmTwo.tboCaseID
forms.frmTwo.tboFieldFour
forms.frmTwo.tboFieldFive
forms.frmTwo.tboFieldSix
 

forms.frmMain.tboCaseID
forms.frmMain.tboFieldSeven
forms.frmMain.tboFieldEight
forms.frmMain.tboFieldNine
forms.frmMain.tboFieldTen
 

tblLog has fields: lngCaseID (number as long) and strFieldOne ... strFieldTen (as text)

For the sequential data entry forms the field lngCaseID is the same.

Is there a better method... apply filter, or using a runquery???

thxs!!
/jh

[mshmyob]

Could you explain what the program is supposed to do, what you exactly want done and tell us the field names (not field1, field2, etc.)

cheers,