Add a new data in Main and Subform ?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: ym (ym_at_discussions.microsoft.com)
Date: 09/23/04 

Date: Wed, 22 Sep 2004 17:01:05 -0700

Hi,

have spent 3/4 of my work day and still having a hard time getting this
right. My Main form (RecordSource = table [Complete]) has only 1 textbox for
entering a DATE item. This textbox is not bound. Included in this main form
is a subform which has a drop down box for names and 3 textboxes for number
entry.

The Main and Subform has a parent/child relationship linked by
[Complete].[Complete ID] = [Complete Detail].[Complete ID]

When the user enter a DATE item and hit the GetData button, I have to search
if this DATE exists in the [Complete] table.

If it exists in the table, then retrieve its child records in the subform
for user to edit or add more child records.

It it does not, then I have to add this DATE as new record into the
[Complete] table and the user can add child records in the subform.

I do not understand Main and Subform manipulation completely. In the case
of a New date, how do I control the Main form to add a new record and then in
the subform the user can add child records and the [Complete Detai].[Complete
ID] will = to the new Complete ID created in the main form.

Would appreciate any help .....

Here is my code for the command to GetData :

Private Sub cmdGetData_Click()
On Error GoTo Err_Handler

    Dim rst As Object
    Dim strSQL As String
    Dim i As Integer
    Dim intParentRecord As Integer

    txtDate.SetFocus
    If IsNull(txtDate) Or Len(txtDate) <= 0 Then
       'do nothing
       Exit Sub
    End If
    
    If Not IsDate(txtDate) Then
        MsgBox LookupMessage(gsLanguage, "EnterValidDateFirst")
        txtDate.SetFocus
        Exit Sub
    End If
    
    strSQL = "SELECT [Complete ID] " & _
             "FROM [Complete] " & _
             "WHERE CompleteDate = #" & ConvertDate(txtDate) & "#"
      
    Set rst = CurrentDb.OpenRecordset(strSQL)
    If Not rst.EOF And Not rst.BOF Then
            boolNew = False
            intParentRecord = rst(0)
            
            strSQL = "SELECT * " & _
                     "FROM [Complete Detail] " & _
                     "WHERE [Complete ID] = " & intParentRecord
            
            Forms![Complete]![Complete Detail Child].Form.RecordSource =
strSQL
                
            Me.Complete_Detail_Child.Visible = True
    Else
            boolNew = True
            Me.Recordset.AddNew
            Me.Complete_Detail_Child.Requery
            Me.Recordset(1) = CDate(txtDate)
            Me.Recordset.Update
            MsgBox ("NEW record")
    End If
    
Err_Handler:
    MsgBox CStr(Err.Number) & " : " & Err.Description
End Sub

Relevant Pages

  • Re: Need to create a search facility
    ... and add a textbox control to it and two command ... buttons cmdFilter and cmdReset. ... add a subform control and set its' SourceObject property to ... Dim strFindLocation As String ...
    (comp.databases.ms-access)
  • Re: Passing textbox data into String for URL
    ... I created a textbox that is unbound, input an address into it, and had ... proper form using the reference to the subform you stated below. ... When I copy and paste the string into an actual browser, ... Dim strCity As String ...
    (microsoft.public.access.formscoding)
  • Re: Passing textbox data into String for URL
    ... I couldn't get the string to store correctly, ... to a textbox and ran the navigate through the textbox and it worked ... proper form using the reference to the subform you stated below. ... Dim strCity As String ...
    (microsoft.public.access.formscoding)
  • Re: refresh an open form using records from another open form?
    ... Yes, I made the linking textbox visible, and yes the record ID's match ... It's as if the subform only wakes up when I click on it?... ... I click a control on the 'frm_Road_Junctions' form. ... Did you set up the master/child link for the subform control containing ...
    (microsoft.public.access.formscoding)
  • Re: Form to search database and return found records
    ... I created a query that pulls in required data from all four tables. ... The way you keep a subform in sync with the main form ... the mainform) is by using the Link Master Fields and Link Child Fields ... identify the fieldin the Master record that point to the child records in ...
    (microsoft.public.access.forms)