Access 2003 to 2007

[Chekotah]

Ok. Has any one ran across this issue that when a data base has been created in 2003 and then saved in 2007 some of the search functions get lossed.



here is my scenario,

I created a data base in Access 2003. I have a form which has a combo box linked to a table that when you enter their employee ID all the other fields on the form populate. However when I save it in 2007 i loose this function. The Combo box is there however that is it.

[John_G]

How does the combo box populate all the other fields on the form? If it uses VBA code to do it, did you recompile it? I found that there can be issues with missing references, and if you don't recompile, you won't find them.

HTH

John

[Chekotah]

Thanks John. It does use VBA code to populate the fields. Can you please explain how to recompile it.

Thanks again.
Chekotah

[John_G]

Hi -

Open any VBA code module - either in a form or by itself - then on the toolbar click Debug - Compile (that is for A2003 - I think it is the same for A2007). You may well find there are compilation errors, with error messages that don't seem to make sense.

While still in the module, click Tools - References. Are there any that are marked ** MISSING ** ? There very likely will be, especially if you get compilation errors. It's just of those things that has to be checked when migrating from A2003 to A2007.

Report back if there are any references that need to be fixed.

John

[Chekotah]

None of the reference marked have the missing. when I compiled it it found no errors. however it still is doing the same issues. any suggestions again.

thank you.

[John_G]

Are you sure the code to populate the other fields is actually running? What event of the combo box does it use? (You can check if the code is running by using a msgbox )

Can you post the VBA used to populate the other fields?

John

[Chekotah]

Here is the code i have for the form. combo 86 is the employee ID number to reference.
I am not sure if this has anything to do with it or not but it is happening after we log on to the db. I have a login form that is to be signed into and then it goes to a switchboard where they choose this form to reference employee information. It is to open to a blank form which it does once they log on. I have it or suppose to be rather that when the employee id is put in it will populate the information in the form. At one time time I had a button on the form for the managers to add a new employee however i have removed that and created a separate form and button on the switchboard. Any recommendations and suggestions to clean this up and working is greatly appreciated.

Private Sub Combo86_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Employee ID] = " & Str(Nz(Me![Combo86], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click

DoCmd.GoToRecord , , acNewRec
Exit_Add_Record_Click:
Exit Sub
Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub
Private Sub Add_New_Employee_Click()
On Error GoTo Err_Add_New_Employee_Click

DoCmd.GoToRecord , , acNewRec
Exit_Add_New_Employee_Click:
Exit Sub
Err_Add_New_Employee_Click:
MsgBox Err.Description
Resume Exit_Add_New_Employee_Click

End Sub
Private Sub New_Hire_Click()
On Error GoTo Err_New_Hire_Click

DoCmd.GoToRecord , , acNewRec
Exit_New_Hire_Click:
Exit Sub
Err_New_Hire_Click:
MsgBox Err.Description
Resume Exit_New_Hire_Click

End Sub
Private Sub Command98_Click()
On Error GoTo Err_Command98_Click

If Me.Dirty Then Me.Dirty = False
DoCmd.Close
Exit_Command98_Click:
Exit Sub
Err_Command98_Click:
MsgBox Err.Description
Resume Exit_Command98_Click

End Sub

[John_G]

Hi -

One error I see immediately -

Set rs = Me.Recordset.Clone

should be Set rs = Me.RecordsetClone

(without the period between Recorset and Clone) for a DAO recordset.

You might want to display an error message (MsgBox) if the findfirst fails.

Try that and see how it goes.

John

[ItsMe]

Beyond the Recordsetclone thing, here is some code that I have tested that uses bookmark. I feel there could be some additional validation done in your afterupdate.

Dim rs As DAO.Recordset
If Not IsNull(Me.ComboboxName) Then
    If Me.Dirty Then
        Me.Dirty = False
    End If
    Set rs = Me.RecordsetClone
    rs.FindFirst "[cust_name] = '" & Me.ComboboxName.Column(0) & "'"
    If rs.NoMatch Then
        MsgBox ("Not found")
    Else
          If rs.Bookmarkable = False Then
          MsgBox "Can't bookmark this record"
          Else
          Me.Bookmark = rs.Bookmark
          End If
    End If
    Set rs = Nothing
End If

Also, I do not see how this ever worked.
rs.FindFirst "[Employee ID] = " & Str(Nz(Me![Combo86], 0))
If Employee ID is text, just use the column example I provided in the code above. If Employee ID is a number, use the column example above and concatenate for a number

rs.FindFirst "[cust_name] = " & Me.ComboboxName.Column(0)

[Chekotah]

Thanks again guys. And help is very much appreciated.

I have made the changes you suggested and i still isn't populating the form. When debugging, the following line is highlighted in yellow. It is a number and when i have my curser of the Me.combo86.column area the number i am testing is there however it is not Pulling the rest of the recordset from the table. Any suggetions.

rs.FindFirst "[Employee_Name] = " & Me.Combo86.Column(0)

[ItsMe]

Can you post the code that you are using for that sub procedure here?

[Chekotah]

ItsMe, Here you go.

Private Sub Combo86_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
If Not IsNull(Me.Combo86) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[Employee_Name] = " & Me.Combo86.Column(0)
If rs.NoMatch Then
MsgBox ("Not found")
Else
If rs.Bookmarkable = False Then
MsgBox "Can't bookmark this record"
Else
Me.Bookmark = rs.Bookmark
End If
End If
Set rs = Nothing
End If
End Sub

[John_G]

Hi -

If I had to guess, I would say you may be doing your search on the wrong field.

In: rs.FindFirst "[Employee_Name] = " & Me.Combo86.Column(0)

Is it really [Employee_Name] you want to use, or should it be Employee_Number? A Name is usually text, but your combo box is giving you a number.

Just a thought

John

[ItsMe]

In your original code you had this
rs.FindFirst "[Employee ID] = " & Str(Nz(Me![Combo86], 0))

Now you are using
rs.FindFirst "[Employee_Name] = " & Me.Combo86.Column(0)

If
Me.Combo86.Column(0)
Is the ID/Key Value and is a number, you want to match the correct Field Name with your combo column one.

Maybe
rs.FindFirst "[Employee ID] = " & Me.Combo86.Column(0)
is the correct field name to use

[Chekotah]

Thank you I apologize for that. I have fixed that.

ok. this is weird -- if i directly to the form it works. if i go though the switchboard that i have is when it is not working. i am getting a "Not Found" Message and will not pull the information. I'm so confused...