I have a select statement on an unbound control on a form that selects from the name field and then adds some more data from a couple other fields and then orders the selection by name. It works great except when there is a duplicate name in the database. Say that John Doe has two accounts, 12345 and 54321 so his name is in the name field twice. It will allow me to select the first John Doe for account number 12345 but not for 54321. How can I write this select statement to allow it to select either of the names from either account number?

SELECT tblMainFin.txtName1, (tblMainFin.txtName1+" ") & tblMainFin.txtSecondName & tblMainFin.AcctNum FROM tblMainFin ORDER BY [txtName1];

Thanks

what type of control?
as a test, can you add the acctnum field as an additional field by itself?

Hi

You'd have separate tables, so that one client cold have many accounts. You'd only have John Doe in the clients table once then, but he could have as many accounts as he wanted or needed.

Of course, if you had several clients with the name John Doe you'd still need a way of differentiating between them but that can easily be done by using some other data that is specific to a duplicate name - eg address (or part of it), SSN etc

Your problem is that the selection needs to be made using the PRIMARY KEY for the record, which I assume must be accountnumber, not one of the value fields(i.e. Account name).

Try this, and make sure the bound column of the list box or combo box, whichever this is, is set to the first column.

SELECT tblMainFin.AcctNum , tblMainFin.txtName1, (tblMainFin.txtName1+" ") & tblMainFin.txtSecondName & tblMainFin.AcctNum FROM tblMainFin ORDER BY [txtName1];

Set the First Column width to 0, so it doesn't display in the list or combo box.

Actually, each customer only has one account normally. In the example I'm trying to work on right now, 54321 is a typo. They want to be able to look at what was entered in the form for 54321 and move it over to 12345 and then run a delete query to delete the 54321 record. The account number is an identifier but not a unique identifer because a spouse or family member might have the same account number.

Account number is not the primary key. There could be multiple people using the same account number but only one account number exists for each customer normally. For example, you migh have an account number with your cell phone plan where you, a spouse and possibly a few other family members are on the same account. In the case I'm talking about 54321 was a type-o. They want to copy the data from 54321 and move it to 12345 and run a delete query to remove 54321 from the database. So, would it look like this?

SELECT tblMainFin.PrimaryKey , tblMainFin.txtName1, (tblMainFin.txtName1+" ") & tblMainFin.txtSecondName & tblMainFin.AcctNum FROM tblMainFin ORDER BY [txtName1];

That's the way to do it, yes.

It lets me select the correct name and account number combo from the drop down list but now I'm having an issue with the on click event after the name is selected. Once they select the name from the drop down, I have a button with the following on click event. Now when I select the name and click the button, the form opens but the data is blank. If I change the select statement back to the way it way, there is data there. Is there something wrong with my on click event?

Private Sub Command13_Click()
Dim stDocName As String, strArgs
Dim stLinkCriteria As String
strArgs = Combo5
stDocName = "frmBTRReceived"

If Not IsNull(Combo5) Then
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit, , strArgs

Else
MsgBox "You must enter a name."

End If
End Sub

Ah, we've probably changed the datatype of the selection in your combo. It WAS a name (string, or text datatype), now it's a PK, which is probably a number based on the Autonumber? If that's the case, you need to change how the strArgs is handled in the open event of the called form.

Would I just change Dim stLinkCriteria As Integer?

There must be something else I need to do because my form is still blank.

Thanks again

Also, this in on the on load even of the form that opens on click:

Private Sub Form_Load()
Dim Temp3 As String

Temp3 = Nz(Me.OpenArgs)
If Temp3 <> "" Then
Me.RecordSource = "Select * from tblMainFin WHERE txtName1 = """ & Temp3 & """"
Me.Requery

DoCmd.Close acForm, "frmSearchResultRec"

End If
End Sub

Hi

You're declaring stLinkCriteria but you're not actually setting it to anything anywhere, so changing it's datatype wouldn't make a difference (IMG:style_emoticons/default/wink.gif)

You use both stLinkCriteria and strArgs in the Openform method, what are you using to filter the form with ?

Also, just FYI, in this line

Dim stDocName As String, strArgs

you're not setting the datatype of strArgs, which would actually be a VAriant as you currently have it. Not that that in itself would make much difference, but if you really want it as a string you'd need to change it to

Dim stDocName As String, strArgs As String

The other thing worth mentioning is that you have

strArgs = Combo5

which would be better off changed to

strArgs = Me.Combo5

assuming that the combo is on the same form as you're running the code - plus Combo5 would be better named to something more meaningful (eg cboSelectName)

Hi

Ahh, just seen you've posted that you're using the OpenArgs bit to filter your form. In which case, you don't need stLinkCriteria at all. It would this line

strArgs = Combo5

that you'd change to reference the correct column of the combo, eg

strArgs = Me.Combo5.Column(1)

Change the 1 to whatever column you need

Something still isn't right. I changed the column to 1 which is my primary key and it's still pulling in incorrect data in the form. Any ideas?

Okay, from my form with the drop down to select the name, my on click button code is, column(1) being the primary key:

Private Sub Command13_Click()
Dim stDocName As String, strArgs As Integer

strArgs = Me.Combo5.Column(1)
stDocName = "frmBTRReceived"

If Not IsNull(Combo5) Then
DoCmd.OpenForm stDocName, , , , acFormEdit, , strArgs

Else
MsgBox "You must enter a name."

End If
End Sub

On the on load event of form frmBTRReceived, this is the code:

Private Sub Form_Load()
Dim Temp3 As Integer

Temp3 = Nz(Me.OpenArgs)
If Temp3 <> "" Then
Me.RecordSource = "Select * from tblMainFin WHERE PrimKey = """ & Temp3 & """"
Me.Requery

DoCmd.Close acForm, "frmSearchResultRec"

End If
End Sub


It's still not working. What is wrong?

Thanks again.

Hi

Couple of things to look at

1 Are you sure that Column(1) of the combo is returning the value you expect ? Column numbering is zero based, so the first 1 is 0, the second is 1 etc etc. Try putting a message box after this line

If Not IsNull(Combo5) Then

so it reads

If Not IsNull(Combo5) Then
Msgbox Me.Combo5.Column(1)

and see what you get. Change the column number until you get the value you're expecting

2 What datatype will the corrct value of Combo5 actually be ? You currently have strArgs As Integer where the strArgs part would suggest it's a string, but then you're defining it as an Integer. If it really is the Primary Key and you're using an Autonumber, then it would need to be a Long datatype and not an Integer. Even if it's not an Autonumber but just some form of numerical value Primary Key, I'd still imagine it would need to be a Long.

Same applies on your opened form - you have Dim Temp3 As Integer which would need changing to the correct datatype. Also you don't need the Me.Requery line. Simply setting the RecordSource will cause the requery, so calling it again is requerying the form twice

3 Once you have your datatype sorted out in the calling code, change the delimiters in the SELECT statement of the opened form to suit

4 Add the Me keyword to your control name, so use Me.Combo5 instead of just Combo5

Private Sub Form_Load()
Dim Temp3 As Long

Temp3 = Nz(Me.OpenArgs)

If Temp3 <> "" Then
Me.RecordSource = "Select * from tblMainFin WHERE PrimKey = """ & Temp3 & """"
Me.Requery

DoCmd.Close acForm, "frmSearchResultRec"

End If
End Sub

It's erroring at If Temp3 <> "" Then

Hi

OpenArgs is passed as a string value, so you'd need to coerce it to a long which is what you've defined the Temp3 variable as

Temp3 = CLng(Nz(Me.OpenArgs,0))

Try changing your code block to

Perfect! Thank you.

(IMG:style_emoticons/default/yw.gif)