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.
Full Version: On A Form Open In Add Mode, Add To An Existng Record
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?
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?
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.
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.
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:
The First line finds the record, the second line fails,
D
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
'DoCmd.GoToRecord , NewTicketSubForm_subform, "", acNewRec
The First line finds the record, the second line fails,
D
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
HTH
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
'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
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"
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"
If your Master/Child link is to the same ID
the subform should be set to enter a new record for that CustomerID
the subform should be set to enter a new record for that CustomerID
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
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
I am having a struggle with this line. Visual basics says "Cant find Table or qurey"
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
CODE
'Append the NewData as a record in the Categories table.
strTmp = "CustomerTableQuery ([CustomerName])" & _
"SELECT """ & NewData & """ CustomerName;"
DBEngine(0)(0).Execute strTmp, dbFailOnError
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
You Insert into a Table not a Query
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
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
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
What you have posted doesn't have End If
The code has an If statement which you need to tell Access to End
HTH
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
'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
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.
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.
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.
zip & attach it?
I think were at the stage where actually seeing the behavior will help us determine what to do.
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.
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
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
' 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
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.