0

I have already searched through and tried all recommendations I can find, but nothing has worked. My problem is that I want to create a search function in a form and press a button to display the search results in a subform WITHOUT opening a new datasheet.

I have written a query which successfully searches for items based on the parameters I give it (the parameters are written in text boxes in the form).

I also have a button in the form which opens the query. But this opens the query in a new datasheet window. Not only does it do that, but I'm fairly certain that doesn't do anything to the subform at all.

This is all inside a larger navigation form.

Edit: I'll include the query code, but it isn't really that important, so I'll put it in a format that shows only a little bit:

SELECT [Car Table].Car_VIN, [Car Table].Car_Class, [Car Table].Car_BodyType, [Car Table].Car_Colour, [Car Table].Car_Make, [Car Table].Car_Model, [Car Table].Car_EngineType, [Car Table].Car_TransmissionType, [Car Table].Car_GPSAvailability, [Car Table].Car_BootSpace, [Car Table].Car_FuelConsumptRate, [Car Table].Car_SeatNumber, [Car Table].Car_GreenStarRating, [Car Table].Car_ANCAPSafetyRating

FROM [Car Table]

WHERE ((([Car Table].Car_VIN) Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![txtVIN] & "*") AND (([Car Table].Car_Class) Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![cmbClass] & "*") AND (([Car Table].Car_BodyType) Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![cmbBodyType] & "*") AND (([Car Table].Car_Colour) Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![cmbBodyType] & "*") AND (([Car Table].Car_Make) Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![txtMake] & "*") AND (([Car Table].Car_Model) Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![txtModel] & "*") AND (([Car Table].Car_EngineType) Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![cmbEngineType] & "*") AND (([Car Table].Car_TransmissionType) Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![cmbTransmissionType] & "*") AND (([Car Table].Car_GPSAvailability) Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![ChGPSAvailability] & "*") AND (([Car Table].Car_SeatNumber) Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![txtSeatNumber] & "*") AND (([Car Table].Car_GreenStarRating) Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![cmbGreenStarRating] & "*") AND (([Car Table].Car_ANCAPSafetyRating) Like "*" & [Forms]![Navigation Form]![NavigationSubform].[Form]![cmbANCAPSafetyRating] & "*") AND ((Abs([car_fuelconsumptrate]-[Forms]![Navigation Form]![NavigationSubform].[Form]![txtFuelConsumption]))<=2) AND ((Abs([car_bootspace]-[Forms]![Navigation Form]![NavigationSubform].[Form]![txtBootSpace]))<=100));

I have tried creating a button that performs a requery on the subform, but the requery didn't do anything when I clicked it, I am guessing because I didn't really reference the query itself anywhere.

I also tried doing a split-view form, but not only did I not get anywhere, I did not want to have a split view.

RIGHT NOW I have a button that runs the query, textboxes which I can enter parameters into (for example if I typed in 100 into the bootspace text field, it would return all values near 100 including 100, or if I also typed in 'kia' into the carmake text field it would return all cars that have a bootspace near 100 and are Kias) and my subform underneath, which is completely blank.

Edit: VBA code

Private Sub Command409_Click()

Me.[Car Table subform1].Form.Requery

End Sub
NoNaMe
  • 6,020
  • 30
  • 82
  • 110
BlueRhapsody
  • 93
  • 2
  • 13

2 Answers2

1

Set the query to the subform-recordsource in the form properties. I assume you have done that already.

In your button place vba code:

Private Sub button_click()
    Me.PUT_SUBFORM_NAME_HERE.Form.Requery
End sub
asdev
  • 943
  • 6
  • 9
  • Sorry, i should have put my vba code above too; i have already done both steps that you have suggested, and when i click the button, simply nothing happens - the subform remains blank. I will edit my post to include the vba code i had – BlueRhapsody Sep 22 '15 at 07:21
  • Okay, that is strange. If you tested the SQL and it worked, there must be another reason. Is your subform bound to the parent form in any way? See it in the subform propertys Link Child Fields and Link Master Fields – asdev Sep 22 '15 at 07:31
  • You can also try to set a simple query or just a table to the recordsource of the subform. If it does not show anything it indicates a problem in the form design – asdev Sep 22 '15 at 07:33
  • both link child fields and link master fields are "Car_VIN" which is my primary key for my 'car table' (which is the table that the query draws data from). I will try your second comment now. Edit: changed record source to Car Table and still no results show. – BlueRhapsody Sep 22 '15 at 07:39
  • If you bind the subform to the PK Car_VIN you can not find other cars than the one on your main form, because the subform can only show the car with Car_VIN. Just remove it and try. – asdev Sep 22 '15 at 07:45
  • It worked! Thankyou so much! It seems to work so far, but i might run into another error in a moment with what im about to try. – BlueRhapsody Sep 22 '15 at 08:00
0

at the end of your search button's on click event write this line of code : 

Main_form_name.sub_form_name.Form.Requery
End Sub

that should do the trick

Benion
  • 80
  • 1
  • 12
  • I tried a few things and all returned errors: [Forms]![navigation form]![find a car form]![Car Table subform1].Form.Requery ^ got error 2465: cant find field 'find a car form' Me.[navigation form]![find a car form]![Car Table subform1].Form.Requery ^ got me error 2465: "cant find field '|1' " [navigation form]![find a car form]![Car Table subform1].Form.Requery same error as above [find a car form]![Car Table subform1].Form.Requery same as above. (exact same errors when i use periods rather than exclamation marks) sorry for poor formatting, didn't realise paragraphs wouldn't appear. – BlueRhapsody Sep 22 '15 at 07:34
  • replace the space in the from name with underscore(_) & give it a try. i.e. instead of [find a car form] try [find_a_car_form] & make sure you spell the form names correctly – Benion Sep 22 '15 at 07:38
  • Tried it with the underscores and got the same result. I am almost definite that i have not misspelt form names. – BlueRhapsody Sep 22 '15 at 07:58