new customer automatic button

[bricklebrit]

Hello,

In an order form [order3], I am attempting to create an automated button that will take the entered [CustomerName] and enter it into a new record in the field [customers].

Is there a simple script to achieve this effect?

Thanks in advance for your help!

 

[Bry12345]

For the OnClick Button event for the new form:

Private Sub Whatever
On Error GoTo Errstuff

Dim strDocName as String
Dim stLinkCriteria as String

stDocName = "frmNewFormtoOpen"

stLinkCriteria = "[YourLinkedField]=" & "'" & Me![YourLinkedField] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit Whatever

Errorstuff: here

End Sub


For the Load event of the new form:

Private Sub Form_Load

Forms!frmNewFormToOpen.YourLinkedField = Forms!frmTheOriginalForm.YourLinkedField

End Sub


Bry

 

[bricklebrit]

Thanks for your prompt help!

I'm getting a runtime error '2501' on the line:


DoCmd.OpenForm stDocName, , , stLinkCriteria



Here is the code I've modified with my field names [CustomerID] and the form I want to open, [CustomersNew]:



Private Sub NewCustomer_Click()


Dim strDocName As String
Dim stLinkCriteria As String

stDocName = "CustomersNew"

stLinkCriteria = "[CustomerNumber]=" & "'" & Me![CustomerNumber] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria


End Sub

 

[LittleSmudge]

I don't recognise Error 2501 - What is the Err.Description ?

As a first look though:-

stLinkCriteria = "[CustomerNumber] = '" & Me![CustomerNumber] & "'"
.. .. will work fine ( you don't need to seperate out the first delimiting ' ) IF [CustomerNumber] is a string

IF Customer number is a number ( Now there's a thought ! ) then use
stLinkCriteria = "[CustomerNumber] = " & Me![CustomerNumber]



G LS

 

[bricklebrit]

Hello!

Thanks for the help --

[CustomerNumber] is a number. Now using the script:

----

Private Sub NewCustomer_Click()


Dim strDocName As String
Dim stLinkCriteria As String

stDocName = "CustomersNew"

stLinkCriteria = "[CustomerNumber] = " & Me![CustomerNumber] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria


End Sub

----

The script gives a error:

Run-time error '3075; Syntax error in query expression '[CustomerNumber] = 117'

Upon debugging, it highlights the line:
DoCmd.OpenForm stDocName, , , stLinkCriteria

(customer number 117 happens just to be the one I have open)

Any ideas?

 

[Bry12345]

Sorry! It's my typing! Take the "r" out of the second line . .

i.e. Dim stDocName as String

This line defines the variable . .which I misspelled . . so when the DoCmd tries to open the form, the variable whose value is the form name doesn't exist.

Let me know if this works!

 

[bricklebrit]

Hello,

Thanks for all the help so far! I don't get any runtime errors, but now when I type in a new customer's name in the CustomerID field, I'm getting the error:

+++

The text you entered isn't an item in the list.

Select an item from the list, or enter text that matches one of the listed items.

+++

Its because I'm selecting the names from the list I suppose. Is there a way to have the NEW CUSTOMER button automatically create an entry with the new name to avoid this problem (and then use the program code you gave me to open it in the [customer] form)?

Thanks again for all the assistance.

 

[Bry12345]

This sounds unrelated to the code you just added. If you are entering the name into a list or drop-down box, check the properties and make sure Limit to List is set to no. - - - -

Bry