Question format when 2 possible entrires exist? 1

[kentwoodjean]

Have a great form that I don't want to garbage up but now realize needs to be adjusted. When the customer calls in and we ask for the "ContractNum", we would expect him to give us the 9 digit number assigned to him. Once inut in the text box, subsequent text boxes will populate based on the corresponding customer history.

Since customer contract numbers have changed this year from Soc.Sec.# to another radomly assigned 9 digit number, it is possible the customer may give us one or the other number.

Is there a way to see this up so that if the old Soc. Sec. # is input, a message might appear saying there is a new number and have that new contract # show up with the appropriate data? Naturally, if the new # was input there would be no need to change the number in this field and the correct data would appear. The message box is no so important, but I need to resolve this old vs new number and yet retain the autopopulation of my other fields. Both the old and new contract numbers are on my membership table.

I am at a lost and would appreciate any help here. Writing code is not my specialty and I am still learning that.

 

[stix4t2]

Use a DLookup on the text in the control to test if it is a SSN number, then if it is process like you described.

 

[kentwoodjean]

Is this the way I would put my DLookup? Again I am not too good at these.

DLookup (“CONTRACT_NUM”, “tblMembership” , “Criteria= ‘ActiveContract#’”)

And if I understood you correctly, I would put it in the control source of the data properties in the text box? If so, I am getting an #NAME? error on my version of the DLookup. Where am I wrong?

 

[Remou]

Maybe:
[tt]DLookup ("CONTRACT_NUM", "tblMembership" , "CONTRACT_NUM=" & Me.[ActiveContract#])[/tt]
Numbers do not need single quotes.

However, you may prefer to do something like:

If IsNull(DLookup ("SSN", "tblMembership" , "SSN=" & Me.[ActiveContract#])) Then
   MsgBox "This is an SSN"
End If

Or even use a recordset, so that you can return more data.

 

[kentwoodjean]

I will work with your suggestions at home tonight and let you know. THAnks.

 

[Remou]

Oops.
MsgBox "This is [red]NOT[/red] an SSN"

 

[kentwoodjean]

Guess I am not doing this correctly as I am not getting the results I am looking for. Unfortunately, I have to have this working by tomorrow.

Is there any chance I could zip up a copy of what I am working on and have someone take a look at it. I actually have 2 problems to solve. (1) the issue of the contract num and the activie contract num and (2) I have a button to point to a status report that I want to be specific to the member I am working with. There may be a problem with the query as it only pulls up 1 report for the same customer each time. I have worked and reworked the query, played with someforms/subreports, and I am getting no where.

It is very haed to explain so if someone could look at this DBase specific to these issues, I could be most grateful.

 

[Remou]

I will see if I can help, but may not be able to. You do mean an Access database don't you, not a Borland dBase?
I think email addresses are discouraged, so this should be clear enough, I guess:
remou_ at hot mail dot com

It may be best to post your query in the Access Queries and JET SQL forum, as well.