Full Version: On A Form Open In Add Mode, Add To An Existng Record
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
Powelldog
Hi all again.
I have one I can't figure out.
I have a form that opens in Add a record mode.
this form has a sub form in it.
it opens and I add a customer name to a field Field name: "CustomerName"
A new record is started, saved as a new customer name.
I need this to also do this on a duplicat "CustomerName" if typed in.
Open the Record in the main form that matches the first matching name.
and start new child records for the existing customer.
the whole form is based on a Query with all the information in it.
any help would be greatly appriciated.
D.
cpetermann
D.
I'm not able to visualize what you have described.
Would it be possible make a copy of your db,
remove any sensitive data--leaving enough "dummy data" to see what you want to accomplish,
then zip & attach your db?
Powelldog
Yes, I can post the code. however I think that something in my form has failed, it is not running any code... like .dropdown is not responding either. I will re-create the form and test again.
will post if this doesn't work for me.
Thanks, And I don't think I need to use a subform. I found out that I don't need to be able to flip through records associated with a customer name.
thanks
D.
Powelldog
Ok I revisited this problem, I am still unable to resolve ether of my problems
On a form with a sub form
At the top of the Main form I search for a customer using a combo box. this works it opens the customer with the correct CustomerId
and displays the first Subform Record.
Oneed the sub form record not to display the first record. I need it to be in Add (New) Record mode. (Linked to the Main forms customer.)
Second Problem
I type a customer name that is not in the list "Not In List"
I just need it to start a new Main form record. using the name I just typed.
I have tried code similar to this:
CODE
DoCmd.SearchForRecord , "", acFirst, "[CustomerID] = " & Str(Nz(Screen.ActiveControl, 0))


'DoCmd.GoToRecord , NewTicketSubForm_subform, "", acNewRec

The First line finds the record, the second line fails,
D
cpetermann
D,
. Set the subform's Property for Data to Data Entry
2. Not in List Event--try this -- after replacing with your table & field names
CODE
Dim strTmp As String
    
    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new Customer?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
    
        'Append the NewData as a record in the Categories table.
        strTmp = "INSERT INTO NameOfYourTable ( NameOfTheTableField) " & _
            "SELECT """ & NewData & """ AS NameOfYourField;"
        DBEngine(0)(0).Execute strTmp, dbFailOnError
        
        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
    End If

HTH
Powelldog
Hmm... I tried your idea, and it does open the subform to a new record, however it is not linked to the main form. As if I click a "Add New" Button.
I am starting with the lookup function only, once I get this part working I will move on to the Not in list part.
the linking criteria is:
main form, "CustomerId"
Sub Form, "CustomerTblID"
cpetermann
If your Master/Child link is to the same ID
the subform should be set to enter a new record for that CustomerID
Powelldog
that is what I thought, however I just did this
on the subform I just added to CustomerTblId Default value = customerinputform.CustomerId
now it seems seems to work.
will now look into the "Not in list" problem
Powelldog
I am having a struggle with this line. Visual basics says "Cant find Table or qurey"
!--c1-->
CODE
'Append the NewData as a record in the Categories table.
        strTmp = "CustomerTableQuery ([CustomerName])" & _
            "SELECT """ & NewData & """ CustomerName;"
        DBEngine(0)(0).Execute strTmp, dbFailOnError

I have tried brackets, quotes and mixing it around,
strTmp = "CustomerTableQuery ([CustomerName])" & _
"SELECT """ & NewData & """ CustomerName;"
what is wrong with the "CustomerTableQuery" Statement?
It is spelled correctly there is a Query of this name.
David
cpetermann
You Insert into a Table not a Query
Powelldog
Ok, that works much better. I didn't know that it had to go into a table.
How it adds a name to the record set, but it gets caught in a loop that says it is not in the list and every time I click on add it puts it in again
There is the code I have so far.
I think the last line has something to do with the problem I tried Me.Refresh, after the Response line. id did not help.
Response = acDataErrAdded
CODE
    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new Customer?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
    
        'Append the NewData as a record in the Categories table.
        strTmp = "INSERT INTO Customer (CustomerName)" & _
            "SELECT """ & NewData & """ AS CustomerName;"
        DBEngine(0)(0).Execute strTmp, dbFailOnError
        
        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
cpetermann
What you have posted doesn't have End If
The code has an If statement which you need to tell Access to End
!--c1-->
CODE
Dim strTmp As String
    
    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new Customer?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
    
        'Append the NewData as a record in the Customers table.
        strTmp = "INSERT INTO NameOfYourTable ( NameOfTheTableField) " & _
            "SELECT """ & NewData & """ AS NameOfYourField;"
        DBEngine(0)(0).Execute strTmp, dbFailOnError
        
        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
                    End If

HTH
Powelldog
End If, is in my code.
I just didn't copy it to the forum.
I am using a Query called "CustomerTableQuery" as the source for the records
I Tried switching it to the "Customer" table to see if it would make a difference, it did not.
David, Thanks for your help with this, I really appriciate it.
cpetermann
Could you make a copy of your db,
zip & attach it?
think were at the stage where actually seeing the behavior will help us determine what to do.
Powelldog
After seeing what you were puting in as code, I Remember some code similar to it from years ago.
with the code example in access, it would state there is text after the ; (the semicolin) in the sql statement. I could not find any extra text.
With the code you provided I could not get it to "Requery" the combo box. I don't see why as I reasurch the code adDataErrAdded it should do exactly that, yet it would not show up in the combo box untill I closed the form and reopened it.
Ofound some Code I used 5 years ago, and it worked! here it is.
CODE
'------------------------------------------------------------
' CboCustomer_AfterUpdate
'
'------------------------------------------------------------
Sub CBOCustomer_AfterUpdate()
    ' Find the record that matches the control.
    DoCmd.Requery
    Me.RecordsetClone.FindFirst "[CustomerID] = " & Me![CboCustomer]
    Me.Bookmark = Me.RecordsetClone.Bookmark
    Me.CustomerName.SetFocus
    Me.CboCustomer = Null
End Sub
'----------------------------------------------------------------------------------------------
Private Sub CboCustomer_NotInList(NewData As String, Response As Integer)
Dim strsql
    MsgBox "Record does not exist.  Creating new Record."
    strsql = "Insert Into Customer ([CustomerName]) values ('" & NewData & "')"
    'MsgBox strsql
   CurrentDb.Execute strsql, dbFailOnError
    Response = acDataErrAdded
End Sub

Thanks for all your help, I would not have been able to get this to work without it.
This is the main input form for my client, she wants it to work exactly a specific way, so I had no choice but to get it right.
Thanky you again, David
cpetermann

Good luck with the remainder of your project!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.