Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Passing a value from a combo box to a text box on another form 1

Status
Not open for further replies.

rydrifter

Programmer
Joined
Jun 3, 2002
Messages
3
Location
US
I'm new to access, and I'm trying to pass a value from a combo box named BillTo on a form named CustLookUp to a text box named Phone on the form new customer2. There is a standard phone number input mask on both fields. Here's my code. It simply doesn't work. any help anyone could provide would be greatly appreciated.

Private Sub BillTo_NotInList(NewData As String, Response As Integer)


Dim stDocName As String
Dim stLinkCriteria As String

Response = acDataErrContinue

stDocName = "new customer2"
stLinkCriteria = "[Phone] = Forms![Orders]!BillTo.Text"
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, , stLinkCriteria

End Sub

Thanks for your time,
Ryan
 
You'll learn this as you go along. WHen you're working with Fields from another form or whathaveyou, you need to state the criteria outside the quotes.

With this:
stLinkCriteria = "[Phone] = Forms![Orders]!BillTo.Text"

Try this:
stLinkCriteria = "[Phone] = '" & Forms![Orders]!BillTo.Text & "'"

If the field is set to the datatype of number, you don't need the single quotes, you can use the following as an example:
stLinkCriteria = "[Phone] = " & Forms![Orders]!BillTo.Text

HTH
Roy
aka BanditWk
Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)
 
Ok, I tried that, and it didn't seem to work. So I set a break point and stepped through the code. It stores the value in the stLinkCriteria variable, but it never seems to make it into the other form. Is there something I need to put into the Form_Load event of the second form?

Thanks,
Ryan
 
Something about your question doesn't seem to ring true. It looks like you want to open a form in "ADD" mode, when a NOT IN LIST event happens on a combo box. Ok, that's cool. But what kind of data is NOT in the list? A name or a phone number? Is your BILL TO field a phone number?

OK, so I type a phone number into the combo box that has an input mask (a questionable practice, but we'll leave that for another time) and it's not there. So my Not_In_List event fires, and I open the "New customer2" form - and I want to pass this PHONE number into the PHONE NUMBER field there?

That's gonna be a little bit tough, because when you go into ADD mode, the record buffer, thus the contents of any bound fields, are cleared. So if you try this:

PhoneNumber = combo_Box_value
OpenForm... acADD


The PhoneNumber field that you just so laboriously populated with your combo box value will be nulled out by the ADD process.

However you should be able to stuff the combo box value in there AFTERWARDS..

After the 2nd form opens, in the CURRENT event for the form, you can refer to the combo box in the other form (so long as it's still open)

Sub Form_Current()
ME!PhoneNumber = forms!Orders!BillTo
End Sub

Of course, this doesn't take in to effect any other times you might use this form and the above code will choke - but this is the general process..

Jim
How many of you believe in telekinesis? Raise my hand...
Another free Access forum:
More Access stuff at
 
Thank you Jim. After a little tinkering around with it I got it to work. It's something that I am going to need to use frequently.

Ryan
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top