How to restrict table to show selective data?

[captgnvr]

D/Sirs

For the sales, I have made a table sale_details.

When I use this table in the main form and item purchased in sub form, I get the full records.

Is there a way to restrict the table to show only for the current month?

I learnt to do that using a query and using the query in the main table. The problem is, can not enter new sales.

 

[SteveSchapel]

Captgnvr,

There is no reason why using a query to restrict the records shown on your form to the current month, would prevent the entry of new records.

How did you construct your query? One way is to make a query, based on your sale_details table, and then in the Field row of a blank column in the query design grid, enter the equivalent of this:
Format([YourDateField],"yyyymm")
... and then in the Criteria of this column, enter this:
Format(Date(),"yyyymm")

(Replace 'YourDateField' with the actual name of the date field you have in your sale_details table.)

Save this query, and then enter the name of the query in the Record Source property of the main form.

 

[captgnvr]

D/Steve
Thanks for the response. It took some time to carry out ur suggestion and by that time u hv logged out. Unable to enter new record as get message "cat go to the specified record; u may be at the end of recordset"

It works fine if I have the table as the record source but not if it is a query.

Wish I get some clue on this.

 

[SteveSchapel]

Captgnvr,

Can you please post the SQL view of your query to your reply? Thanks. Also, can you also please give some details of what is actually happening at the time you receive this error message -- do you have some code or macro that is being activated, or how are you going about trying to add your new record?

 

[captgnvr]

D/Steve
Must thank you for the guidance. I reconstructed the whole thing and made the query based on the table and now the records on the form is restricted to staff who have purchased for current month. I lost track of what I did bcos I was trying out so many queries for the stock taking, sales amount etc. Now thanks to you I am back on track with the records nicely being shown only for current month. This thread can be considered closed out. (sorry for delay in responding as had some cargo operations)

 

[SteveSchapel]

Captgnvr,

Thanks for letting us know. I am very pleased to know that you have got something working nicely now, and I am gratified to learn that I was able to provide some help.