By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

mainform and subform

P: 12
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
Expand|Select|Wrap|Line Numbers
  1. 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
Feb 23 '09 #1
Share this Question
Share on Google+
7 Replies


mshmyob
Expert 100+
P: 903
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,
Feb 23 '09 #2

Expert 100+
P: 1,287
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.
Feb 23 '09 #3

P: 12
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 
  3.  
  4. stDocName = "frmTwo" 
  5.  
  6.     stLink1 = "SELECT * FROM tblCallLog " _ 
  7.             & "WHERE lngCaseID = " 
  8.     stLink2 = stLink1 & Chr(39) & Forms!frmOne!tboCaseID & Chr(39) 
  9.  
  10. 'open record 
  11.  
  12.     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

Expand|Select|Wrap|Line Numbers
  1.  
  2. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 
  3.  
  4. stDocName = "frmTwo" 
  5.  
  6. stLink1 = "SELECT * FROM tblCallLog " _ 
  7.             & "WHERE lngCaseID = " 
  8.     stLink2 = stLink1 & Chr(39) & Forms!frmOne!tboCaseID & Chr(39) 
  9.  
  10. 'open record 
  11.  
  12. DoCmd.OpenForm stDocName 
  13.  
  14. Forms.frmTwo.Form.RecordSource = stLink2 
  15.  
  16.  
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
Feb 23 '09 #4

mshmyob
Expert 100+
P: 903
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,
Feb 23 '09 #5

Expert 100+
P: 1,287
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.
Feb 23 '09 #6

P: 12
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:

Expand|Select|Wrap|Line Numbers
  1. forms.frmOne.tboCaseID
  2. forms.frmOne.tboFieldOne
  3. forms.frmOne.tboFieldTwo
  4. forms.frmOne.tboFieldThree
  5.  
Expand|Select|Wrap|Line Numbers
  1. forms.frmTwo.tboCaseID
  2. forms.frmTwo.tboFieldFour
  3. forms.frmTwo.tboFieldFive
  4. forms.frmTwo.tboFieldSix
  5.  
Expand|Select|Wrap|Line Numbers
  1. forms.frmMain.tboCaseID
  2. forms.frmMain.tboFieldSeven
  3. forms.frmMain.tboFieldEight
  4. forms.frmMain.tboFieldNine
  5. forms.frmMain.tboFieldTen
  6.  
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
Feb 23 '09 #7

mshmyob
Expert 100+
P: 903
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,
Feb 23 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.