Query not producing new records added to a table?

[Barrelwaves]

Hello,
I have created a form to enable data to be easily added to the database table, however does anybody know why the query that looks up the records from the table does not return the new entered records, only what was originally in the table?
Thanks

 

[rsmonkey]

you need to refresh the form.. so after the sql has been executed you need to refresh what ever is displaying your records etc.. so say its a listbox.

Me.listbox.Requery etc.. etc.. you get the idea

 

[Barrelwaves]

doesn't seem to be working, already have the requery in place, the data is entered into the table fine but still not showing up with the query?

 

[rsmonkey]

a query is just a filtered personification of data within a table so the reason why its not working is either you have your query wrong or your requery in the wrong place. Thats all i can say without being able to see the db!

 

[Barrelwaves]

could you explain where the requery is supposed to be located please?

 

[rsmonkey]

well again thats hard to fathom without see'ing at least the structure of your form in which your trying to display this data. Is it whithin a listbox or a subform or are your simply trying to populate a combo-box. In all of these its the RecordSource or RowSource that you want to requery. This is the query which collates the data for that paticular object/control. So where is your record source held isit it within the control i.e. is it placed in the record source dialogue box in the properties for that control or is it held within the VBA code. Thus it would be helpful to at least explain the structure of your form so we can give you an idea where your Requery should be placed. Better still would be for you to post your db.

 

[Barrelwaves]

I would post it, however it is far too large, even when fully compressed!
Basically there is a single table that holds all data regarding car head gasket sets, i then have a query that displays all the information onto a subform with cbo boxes to carry out a refined search of the records, the cbo boxes are located on the form which are all working fine, with a search and reset button also the results are filtered and reset no problem. There is then another form for entering new records into the table, this works fine but the new records are not displayed on the query subform when opened again. So your saying that the 'control source' properties for the cbo boxes on the search results form need to be requeried? is this right?

Thanks for your help, driving me mad!!

 

[Rabbie]

Do you just have one table?

If so you probably need to read up on Data Normalisation and database design. If you just need a single table then Excel is more suitable.

 

[Barrelwaves]

Im afraid not, all the data came from excel sheets and we need this interactive database as it is being used throughout the office as a quick and easy way to find gasket information, its working fine, just how we want, its only this problem of when entering new records they are not being displayed by the query. There are other tables but the query related to this problem only looks at one table.

 

[Rabbie]

Barrelwaves, Thanks for clarifying that. It had me a little worried.

 

[rsmonkey]

haha im being a muppet i swear so its the combo boxes on the search form that are not displaying new records when they've been added? this is an easy enough fix if i have got your problem right simply add this to your code:

Priveate Sub Form_Load()

Me.cboname1.Requery
Me.cboname2.Requery
Me.cboname3.Requery

End Sub

obviosuly cboNameX refers to the name of your Combo-Boxes. What this does is refresh the query that populates your combo-boxes & the code above will do this on the form being opened. If you have other control objects that run off a query then you'l want to requery them aswell.

hope this helps. Oh and btw wen i asked for the db i didnt mean the whole thing just make a quick copy delete all but say 3 or 4 records and zip it up so the file size would never be more than a couple hundred kb's!
cheers

 

[Barrelwaves]

I see! but thanks this has done the trick i think, time to go home now so if i have any further problems ill be back on here tomorrow!!
thanks for all your help

regards
Paul