SetFocus, Requery not updating form control (1 Viewer)

[alexandria]

I can't figure out what's wrong with this coding. On my first form (New_SelectName), the user selects their name from a combo box and clicks OK. This name and a generic ProjectName are added to the "NewProjects" table, and a second form opens for the user to enter their information into. I have the following query set as the second form's Record Source:

SELECT NewProjects.ProjectName, NewProjects.Researcher, NewProjects.*
FROM Economists INNER JOIN NewProjects ON Economists.Researcher = NewProjects.Researcher
WHERE (((NewProjects.ProjectName)="Test") AND ((NewProjects.Researcher)=[Forms]![New_SelectName]![nsResearcher]));

The query and INSERT INTO statement (see below) both work on their own, but the Researcher name and ProjectName do not appear in their controls on the second form. I guess the filter isn't working properly. I used this SetFocus/Requery business on a different set of forms without a problem, so I can't understand why it is not working here.

Private Sub nsOK_btn_Click()
DoCmd.SetWarnings (False)
Dim strSQL As String
strSQL = "INSERT INTO NewProjects (Researcher, ProjectName) VALUES ('" & Forms!New_SelectName!nsResearcher & "', 'Test');"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings (True)

DoCmd.OpenForm "New_EnterProject"
With Forms!New_EnterProject
.SetFocus
.Requery
End With
End Sub

 

[stopher]

I tested you code and it works fine for me.

I notice you have a join in your source query linking researcher name. I suggest you test that both the combo and the table are returning the same information e.g. make sure you are picking up the right column of your combo and also that your table is not to be a lookup.

hth
Chris

 

[alexandria]

Thanks for the reply. The initial combo box and the query both act as expected. The appropriate name and the generic "Test" appear in their fields in the table when I select a name in the combo box, and when I run the query they both also appear. And, no, none of my tables are lookup tables.

Any other help/suggestions would be appreciated!

 

[stopher]

Can you post your database?

 

[alexandria]

Here is my database:

 

[stopher]

Here's you database with the problem fixed. I couldn't find what was wrong with the form. The form did indeed seem to not be displaying the source. I fixed it by creating a new form and copying all the controls and code over.

By the way, you can filter a form when you use the OpenForm command like this:
DoCmd.OpenForm "New_EnterProject",,,"[ProjectName]=" & "'Test'"

Also, I don't really get why you need two forms? Why not have the user enter the Economist and ProjectName in the NewProjects form?

hth
Chris

 

[alexandria]

Thanks. I guess my form was corrupt or something.