Retrieve a row in a table in Access, and populate a form

[Schwiz]

Hey everyone, I have a little bit of a dilemna.

I have a form that I am using to enter/view specific file information. From a drop down menu the user can selection File_ID, and based on what the user selects I want to pull up the rest of the information in that table row where File_ID = the ID selected by the user. I am having a bit of trouble getting the form fields to populate with this information.

Here is what I have so far:

Dim fileID As Long
fileID = Me.File_ID

Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("File Information")

Dim mySelect As String
Dim myFrom As String
Dim myWhere As String
Dim mySQL As String

mySelect = "SELECT * "
myFrom = "FROM [File Information] "
myWhere = "WHERE [File Information].File_ID = """ & fileID & """"
mySQL = mySelect & myFrom & myWhere & ";"

DoCmd.GoToRecord acDataForm, "File Information Form", acGoTo, mySQL

I keep getting an error saying that my datatype is wrong. Any suggestions on what I am doing wrong? Thanks in advance.

 

[SkipVought]

Did you intend to post in forum705 or forum702?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Something like this ?

Me.Recordset.FindFirst "File_ID=" & Me!File_ID

Assumptions: the form is bound to [File Information] and Me!File_ID is unbound.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Schwiz]

@Skip, I'm sorry I'm still trying to get used to this forum and I did not see that section.

@PHV, I did try that line of code. The form is bound to [File Information] but File_ID is also bound, therefore it did not work correctly.

I got this error: Run-time error '3426': This action was canceled by an associated object.

 

[PHV]

A combo used as a navigation shouldn't be bound ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Schwiz]

When I remove its 'Control Source' and execute that line of code it says that "File_ID is not recognized as a field name or expression". But File_ID is the name of the drop down.

 

[Schwiz]

I was able to figure this out:

Dim mySQL As String
mySQL = "SELECT * FROM [File Information] " & _
"WHERE [File Information].[File ID] = " & fileID & ";"
Me.RecordSource = mySQL
Me.Requery