Query to existing form

[shall]

Ok I am using Access 2007 I have 2 forms that I use to add employees and also display all their info. I have also made a search form and a query to pull the results. What i am trying to do is to attach the query to the forms as well so that I can still enter the data but if i need to search it will display the employee's information on both forms.

I assume that I can still use the macro openform with data mode on add but i dont know if that would interfer with the query portion.

 

[boblarson]

You don't want data mode as add. You will want EDIT so that all records that meet your query criteria will be there. If you use add then none of them will show up. It is like setting the form's Data Entry property to Yes.

 

[shall]

You don't want data mode as add. You will want EDIT so that all records that meet your query criteria will be there. If you use add then none of them will show up. It is like setting the form's Data Entry property to Yes.

the data mode is not set on the search form but on my switchboard that has a button to add a student and that macro has the mode set. Would that still cause a problem?

 

[boblarson]

You can set the form open in the search form for this part and use edit. It won't affect your switchboard when it opens the same form.

 

[shall]

ok but what about the other part? getting the query tied to the form

 

[boblarson]

What is stopping you from just binding the form to the query and then requery the form, if it is already open, or just opening it to view the results. If the query has no criteria on it, you can add criteria in the DoCmd.OpenForm command like this:

Dim strWhere As String
 
strWhere = "[Field1Name]=" & Me.Something & " And [Field2TextField] = " & Chr(34) & Me.OtherControl & Chr(34) & " And [MyDateField] Between #" & Me.StartDate & "# And #" & Me.EndDate & "#"
 
DoCmd.OpenForm "FormNameHere", WhereCondition:= strWhere

And there you go.

 

[shall]

Being a newbie at all this i guess i am just getting a little confused with it all. I probably know how to do this but drawing a blank. How do i bind the form that is already made to a query? Also if I bind the form will it still allow me to add new information?

I already have the search code steup which is below:
Dim strWHERE As String

If Len(Me.FName & vbNullString) > 0 Then
strWHERE = strWHERE & "[FName] =" & Chr(34) & Me.FName & Chr(34) & " AND "
ElseIf Len(Me.LName & vbNullString) > 0 Then
strWHERE = strWHERE & "[LName] =" & Chr(34) & Me.LName & Chr(34) & " AND "
End If

Debug.Print strWHERE

DoCmd.OpenForm "frmEmployData", acNormal, , strWHERE
DoCmd.OpenForm "frmEmployAtt", acNormal, , strWHERE

 

[boblarson]

If the form is bound to the query, if the query is updatable then the form will allow you to add or edit new information.

For your strWHERE you shouldn't be using ElseIF. You should have two separate IF statements for them:

Dim strWHERE As String

If Len(Me.FName & vbNullString) > 0 Then
   strWHERE = strWHERE & "[FName] =" & Chr(34) & Me.FName & Chr(34) & " AND "
End  If

If Len(Me.LName & vbNullString) > 0 Then
    strWHERE = strWHERE & "[LName] =" & Chr(34) & Me.LName & Chr(34) 
End If
 
If Right(strWHERE, 5) = " AND " Then
   strWHERE = Left(strWHERE, Len(strWHERE) - 5)
End If

Debug.Print strWHERE

DoCmd.OpenForm "frmEmployData", acNormal, , strWHERE
DoCmd.OpenForm "frmEmployAtt", acNormal, , strWHERE

And in the last IF you don't need to add AND to it, if that is the last one. Also, you would need to check to see if the last part is AND (if only the first name box was filled in) and then remove it if there is no last name.

 

[shall]

Ok I added the follow code to the on_Click and it doesn't do anything I tried usiing the dummy names i set up and just leaving it blank but nothing is happening am i missing something?

I have the FName and LName bound to a query but even making them unbound it still not searching. Please help.

Dim strWHERE As String
If Len(Me.FName & vbNullString) > 0 Then
strWHERE = strWHERE & "[FName] =" & Chr(34) & Me.FName & Chr(34) & " AND "
End If
If Len(Me.LName & vbNullString) > 0 Then
strWHERE = strWHERE & "[LName] =" & Chr(34) & Me.LName & Chr(34)
End If

If Right(strWHERE, 5) = " AND " Then
strWHERE = Left(strWHERE, Len(strWHERE) - 5)
End If
Debug.Print strWHERE
DoCmd.OpenForm "frmEmployData", acNormal, , strWHERE
DoCmd.OpenForm "frmEmployAtt", acNormal, , strWHERE

 

[shall]

If the form is bound to the query, if the query is updatable then the form will allow you to add or edit new information.

For your strWHERE you shouldn't be using ElseIF. You should have two separate IF statements for them:

Dim strWHERE As String
 
If Len(Me.FName & vbNullString) > 0 Then
   strWHERE = strWHERE & "[FName] =" & Chr(34) & Me.FName & Chr(34) & " AND "
End  If
 
If Len(Me.LName & vbNullString) > 0 Then
    strWHERE = strWHERE & "[LName] =" & Chr(34) & Me.LName & Chr(34) 
End If
 
If Right(strWHERE, 5) = " AND " Then
   strWHERE = Left(strWHERE, Len(strWHERE) - 5)
End If
 
Debug.Print strWHERE
 
DoCmd.OpenForm "frmEmployData", acNormal, , strWHERE
DoCmd.OpenForm "frmEmployAtt", acNormal, , strWHERE

And in the last IF you don't need to add AND to it, if that is the last one. Also, you would need to check to see if the last part is AND (if only the first name box was filled in) and then remove it if there is no last name.

I am still not having any luck with this code I have changed it a little as i changed the FName to txtFName and changed it to a combo box I also got rid of the LName control and as such adjusted the code to reflect this. The problem I am coming across is nothing happens when I press the Search button please help I am probably missing something easy I am sure but can't find it. Thanks.

 

[boblarson]

Can you post a copy of your database?

 

[shall]

I can and here it is but it is in 2007 since I am using the attachments feature I can't downgrade

Don't worry about the data as it is all dummy info.

 

[boblarson]

okay, a few changes

1. Look at the combo's row source now.

2. Look at the code.

 

[shall]

Thanks Bob works great.

 

[shall]

One more problem maybe you can help me on Bob on the same DB if you pull up the qryEmployAtt and try to add more than 1 field i get an error message i am not sure what i did wrong there either. Thanks again for your help from before.

 

[boblarson]

Well, it would appear that your query is not updatable.

 

[shall]

oh ok thanks been staring at this until i was cross-eyed helps to have another set to look thanks. I was also able to get that working as well.