Form is changing table data

mikesp1234

Registered User.
Local time
Today, 01:43
Joined
Aug 28, 2013
Messages
16
I have a form that allows users to click on an item in a listbox and it brings them to the selected record in another form. However, everytime I close the form and open it, it changes the client name in the list to the client ID.

List is set up like this

Client ID | Client Name| Order Date
1 Mike 2013-08-04
2 Jon 2013-08-15
3 Mark 2013-08-17
ETC...

Turns into this on close - Changes the client name to the ID of the last item slected before close
Client ID | Client Name| Order Date
1 Mike 2013-08-04
2 3 2013-08-15
3 Mark 2013-08-17
ETC...


Select statement is:
SELECT Client.[Client ID], Client.Client, Client.orderDate FROM Client ORDER BY Client.orderDate;

Code is:
Option Compare Database

Private Sub Command22_Click()
'opens the form with my subform that holds the table data
On Error GoTo Err_Command22_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Client1"

stLinkCriteria = "[Client]=" & "'" & Me![ClientLB] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command22_Click:
Exit Sub
Err_Command22_Click:
MsgBox Err.Description
Resume Exit_Command22_Click

End Sub
Private Sub Command23_Click()
'Delete record button
On Error GoTo Err_Command23_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit_Command23_Click:
Exit Sub
Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click

End Sub

Private Sub Form_GotFocus()
'refreshes the listbox
Dim frm As Form
Set frm = Forms("Main")
frm.ClientLB.Requery
End Sub
Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Client1"

stLinkCriteria = "[Client ID]=" & Me![ClientLB]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command25_Click:
Exit Sub
Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Private Sub Command28_Click()
On Error GoTo Err_Command28_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Client1"

stLinkCriteria = "[Client ID]=" & Me![ClientLB]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command28_Click:
Exit Sub
Err_Command28_Click:
MsgBox Err.Description
Resume Exit_Command28_Click

End Sub


Appriciate the help!!!!
 
It would be nice if you used descriptive names for your commands rather than numbers.

Some remarks would also help. Not a lot just one line to say what the Sub is going to do and when.
 
Sounds like the listbox is bound to the field in the table. A search listbox shouldn't be bound.
 
Yeah Rain ,my commenting needs work lol. How do I unbound the listbox Paul?
 
Try removing the control source.
 
Yeah Rain ,my commenting needs work lol. How do I unbound the listbox Paul?

Your naming conventions are more important than your comments.

Think about comming back to your Code in 6 - 12 Months. Will you understand what Command 21 does.

Have a read of the attached word document.
 

Attachments

Users who are viewing this thread

Back
Top Bottom