UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> On A Form Open In Add Mode, Add To An Existng Record    
 
   
Powelldog
post Mar 15 2012, 11:08 PM
Post #1

UtterAccess Veteran
Posts: 394
From: Colorado
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.

This post has been edited by Powelldog: Mar 15 2012, 11:43 PM
Go to the top of the page
 
+
cpetermann
post Mar 16 2012, 10:31 AM
Post #2

UtterAccess Editor
Posts: 4,785
From: Omaha, NE USA
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?
Go to the top of the page
 
+
Powelldog
post Mar 16 2012, 01:50 PM
Post #3

UtterAccess Veteran
Posts: 394
From: Colorado
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.

I 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.
Go to the top of the page
 
+
Powelldog
post Mar 16 2012, 10:57 PM
Post #4

UtterAccess Veteran
Posts: 394
From: Colorado
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.

I need 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
Go to the top of the page
 
+
cpetermann
post Mar 17 2012, 02:47 AM
Post #5

UtterAccess Editor
Posts: 4,785
From: Omaha, NE USA
D,

1. 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
Go to the top of the page
 
+
Powelldog
post Mar 17 2012, 10:56 AM
Post #6

UtterAccess Veteran
Posts: 394
From: Colorado
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"
Go to the top of the page
 
+
cpetermann
post Mar 17 2012, 11:00 AM
Post #7

UtterAccess Editor
Posts: 4,785
From: Omaha, NE USA
If your Master/Child link is to the same ID
the subform should be set to enter a new record for that CustomerID
Go to the top of the page
 
+
Powelldog
post Mar 17 2012, 12:05 PM
Post #8

UtterAccess Veteran
Posts: 394
From: Colorado
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.

I will now look into the "Not in list" problem
Go to the top of the page
 
+
Powelldog
post Mar 17 2012, 01:26 PM
Post #9

UtterAccess Veteran
Posts: 394
From: Colorado
I am having a struggle with this line. Visual basics says "Cant find Table or qurey"

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
Go to the top of the page
 
+
cpetermann
post Mar 17 2012, 01:33 PM
Post #10

UtterAccess Editor
Posts: 4,785
From: Omaha, NE USA
You Insert into a Table not a Query
Go to the top of the page
 
+
Powelldog
post Mar 17 2012, 07:30 PM
Post #11

UtterAccess Veteran
Posts: 394
From: Colorado
Ok, that works much better. I didn't know that it had to go into a table.

now 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
here 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
Go to the top of the page
 
+
cpetermann
post Mar 17 2012, 08:02 PM
Post #12

UtterAccess Editor
Posts: 4,785
From: Omaha, NE USA
What you have posted doesn't have End If
The code has an If statement which you need to tell Access to End

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
Go to the top of the page
 
+
Powelldog
post Mar 17 2012, 10:27 PM
Post #13

UtterAccess Veteran
Posts: 394
From: Colorado
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.

This post has been edited by Powelldog: Mar 17 2012, 10:37 PM
Go to the top of the page
 
+
cpetermann
post Mar 18 2012, 01:15 AM
Post #14

UtterAccess Editor
Posts: 4,785
From: Omaha, NE USA
Could you make a copy of your db,
zip & attach it?

I think were at the stage where actually seeing the behavior will help us determine what to do.
Go to the top of the page
 
+
Powelldog
post Mar 18 2012, 10:36 AM
Post #15

UtterAccess Veteran
Posts: 394
From: Colorado
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.

I found 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
Go to the top of the page
 
+
cpetermann
post Mar 18 2012, 04:55 PM
Post #16

UtterAccess Editor
Posts: 4,785
From: Omaha, NE USA
(IMG:style_emoticons/default/yw.gif)

Good luck with the remainder of your project!
Go to the top of the page
 
+
« Next Oldest · Access Forms · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 21st May 2013 - 02:01 AM