On Not in List, complication, passing related info to another form.

[idd]

Hi all,

Access 2000 user.


The short Question
How do I get the actual text of a business name from a combo box on the enquiry form to be passed to a combo box on the contacts form, or how do i get it to resolve the id which it picks up from FRM_Enq to a name in the combo box on FRM_Contacts.

If you want the long version of the question with more details then read on....

I am creating a CRM style system.

I have a form for enquiries, on the Enquiry form a user enters the enters the business name in a combo box and if the name is not in the list then it prompts to create a new entry for the business. It sends the name entered to the business details form and allows the user to enter the rest of the details for the business and then returns them to the enquiry form.

This works Fine !!

The next field on the enquiry for is for the contact name which is also a combo box, similar function to above, on not in list

This part works fine as well.

The Problem
The contact is associated with a business and, the user has already entered the details of the business on the enquiry screen and if it is a new business then also the full details have been entered on the business details form.

What I am trying to do is send the business name from the combo box on the enquiry form to a combo box on the contacts form.

I have sent the contact name info as openargs. I tried using the WHERE option in the OPENFORM command to set the business ID to that of the Enquiry form with no luck. (-:

I tried using something like the following in the where clause

forms!FRM_Contact.bid = me.bid
[forms]![FRM_Contact].bid = me.bid
forms.FRM_Contact.bid = me.bid

none of the above seem to work.

so What I have done is pass the info with the openargs along with the new contact name info

so it look like this

DoCmd.openform "FRM_Contact", acNormal, , , , acDialog, Me.BID & "," & NewData

It passes both bits of information through, I have tested this and have managed to separate the bits using Mid,Left, Instr, Len etc.

but the actual data it is passing for the business id is the primary key for the business which is a number.

in the combo box the key fields are hidden, so if for eg. I try and assign the value 3 to the business id combo box on the contacts form it wont show the business related to that id instead it will show the value 3 as the text of the business name, (First visible Column).

How do I get the actual text of the business name to be passed to the contacts form, or how do i get it to resolve the id to a name in the combo box.

I hope this makes sense.

Idd

 

[idd]

I got the answer,

I stuck with sending the Business ID through with the OpenArgs from the Enquiries form to the Contacts form.

On the Contacts form I used a dlookup to find the information for the business name, then set the text of the business name combo box to the result of the dlookup.

mission successful.

the code is below.


--------------------------------------------
start of code
--------------------------------------------


' this bit is in the enquiries form on the not in list event of a combo box.
'on the third line from the bottom it sends the business
'ID and the contact name info to the contacts form as openargs.



Dim msg, MyType As String

MyType = "Contact"
msg = NewData & " is not a " & MyType & " in the database," & vbCr & vbCr & _
"Would you like to add " & NewData & " as a new " & MyType & " ?" & _
vbCr & vbCr & " Click YES to Add or NO to re-type"

If MsgBox(msg, vbYesNo + vbDefaultButton1 + vbQuestion) = vbNo Then
SendKeys "{esc}"
Else
DoCmd.openform "FRM_Contact", acNormal, , , , acDialog, Me.BID & "," & NewData
Response = acDataErrAdded
End If

--------------------------------------
end of code
--------------------------------------



this next bit is the little dlookup bit which finds the right info to place in the Business ID combo box, its in the contacts form OPEN event.

--------------------------------------
Start of code
--------------------------------------


NewBid = DLookup("[org_name]", "tbl_Business", "[bid] = " & mybid)

--------------------------------------
End of code
--------------------------------------


this is the full code behind the contacts form OPEN event in case your wondering where I got the var mybid from in the dlookup function.

--------------------------------------
Start of code
--------------------------------------

Private Sub Form_Open(Cancel As Integer)
Dim NewData, StrFname, StrSurname, twonames, NewBid As String
'this bit assigns any info in the openargs to
'a var called newdata
NewData = Me.OpenArgs
'checks to see there is anything in this newdata
If Len(NewData) > 0 Then

'this block separates the Business ID
'from the contacts name which was sent
mybid = Left([NewData], InStr([NewData], ",&quot - 1)
NewData = Mid(NewData, Len(mybid) + 2)

'This block uses the dlookup to find the business name from
'the business table which corresponds to the Business ID
'then it assigns the text to the BID combo box
NewBid = DLookup("[org_name]", "tbl_Business", "[bid] = " & mybid)
With Me.BID
.SetFocus
.Text = NewBid
End With


'This block check to see if the name has two parts ie a space in it
'if so assign 1st part to forename
'and 2nd part to surname
'otherwise everything is forename
twonames = InStr(NewData, " &quot > 0
If twonames = True Then
StrFname = Left([NewData], InStr([NewData], " &quot - 1)
StrSurname = Mid([NewData], InStr([NewData], " &quot + 1)
Else
StrFname = NewData
End If

'assigns the forename to fname field with propercase
With Me
.FName.SetFocus
.FName = StrConv(StrFname, vbProperCase)
.SName.SetFocus
End With

'assig the surname with propercase to sname if there is two parts to name

If twonames = True Then
Me.SName = StrConv(StrSurname, vbProperCase)
Me.Salutation.SetFocus
End If

End If
end sub()


--------------------------------------
End of code
--------------------------------------

If someone knows of a better way then let me know by posting it here.

idd