Select Record from Query

[kappa]

Hi All,
I hope I can explain what I am trying to achieve..
I have a "Top 1" query (Qry_Avail_StockItem), which selects the next available record in a table.
I have a main form (Frm_MasterStock) with a command button "Add Record".
If the user clicks the "Add Record" button, I need the form to go to the record that the query has selected.

Thanks in Advance..

 

[Tim L]

Kappa,

why are you using a query to deteremine the 'next available' record? If you have used the wizard to build the Add Record button this will always take you to the next new record anyway.

Tim

 

[kappa]

Hi Tim,
The "Add Record" tag is only for the user..
The button is actually editing an existing record that is empty except for 2 fields. The reason for this is, I can only hold 1023 records in this particular table. A "new" record cannot be added. Once a record is updated/edited in this table, a field is flagged as "not available". The user can enter an Alias Name on any record, but each record has it's own primary ID 1 - 1023.
I need to the same in reverse for "Delete Record", where I need to remove the data in the table except for the primary ID and flag the record as "Available". My query sorts by the Available / Not Available field.
I hope this expalins what I an trying to do..

 

[Tim L]

A much clearer explanation, thanks. Why don't you try the 'go to specified record' combo box? You have a unique field for each record, so that should not be a problem. You can base the recordsource for the combo on a query that only gives the 'available' records, for the 'add (edit) record' button you are talking about. You could then also have a query for those records that are 'not available' for the deletion. You could, for example, have radio buttons that allow the user to choose between add/delete and as they are selected set (and requery) the recordsource for the combo box accordingly. Just a suggestion.

As an extension you could have a choice of the 'alias' name or the record number from the combo, although you will still need to use the record number to go to the record (or should set the no duplicates for the alias field - the problem then would be blank alias's...).

If you don't want the user to select from a list you could make use of the code behind the 'go to specified record' combo, in conjunction with your button, just have it automatically go to the 'top' record (determined by the query and matching on one of the empty fields). Can't see any point in reinventing the wheel.

Tim

ps: what's constricting the table to 1023 records?

 

[kappa]

Thanks Tim,
I did try the combo box idea initially, and it worked fine..
but I do not want to give the user the option to to see or select the record number.
This is why I would like to use a command button ( as you suggested in your last paragraph).
I am trying to get a Command Button "On_Click" event to give me the same result as my test combo box did.
I tried different ways with the "GoToRecord acDataQuery" code but could not quite get it to work,(I am fairly new to Access code). I do think this is the way to go though..

This is what I had in my test combo box, which worked fine..
Unbound
Rowsource = SELECT [Qry_Avail_StockItem].[Stock_Item_No] FROM [Qry _Avail_StockItem];

The code in the "After Update" Event of my test Combo box is:-
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Stock_Item_No] = " & Str(Me![Combo90])
Me.Bookmark = rs.Bookmark

Any help on the "Go to specified record" would be grateful.
Thanks again for your help

Don

 

[Tim L]

    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Stock_Item_No] = " & [B]Str(Me![Combo90])[/B]
    Me.Bookmark = rs.Bookmark

Don,

You're on the track that I was thinking about.

If you can make the code in bold refer to the next available Stock_Item_No then it should all work. I haven't tried this, but you might be able to put a hidden (i.e: visible = No) control on the form, which shows the Stock_Item_No from your next available query. (Don't forget to refresh the query either straight after, or just before (would be my preference), it is accessed).

Tim

 

[kappa]

Thanks Tim,
That is the part I am stuck on.. Linking this line to my Query

BTW: The reason for limited records is :-
The project I am working on is a Liquor Stock Control Program ( for Hotels )
Each Plu Item ( Child ) ie. Pint of Boddingtons has a stock link code field.
This four digit number denotes (a) what size the child item is.. ie Pint=570ml
and (b) what parent this plu is linked to ie Keg Boddingtons 49000ml.
I have worked out an algorithm that combines (a) & (b) into a unique number.
Parameters Stock Code Range 1 - 1023, Size code 1-63.
Also when this child item is read back into my stock program in a sales format, I can get the qty of sales for this item, decode the stocklink number to obtain the parent & unit item size...

Don

 

[kappa]

I have tried your advice and placed a listbox on the form ( which can be set to visible = No).

Rowsource = Qry_Avail_StockItem
This listbox works ok, displaying the correct record number from my query.
I cannot however, select this value in code, behind my command button.
I have tried:-
rs.FindFirst "[Stock_Item_No] = " & Str(Me![List94]), which results in a "Null" error.
Is this somthing to do with the Control Source, which is currently none.

Don

 

[kappa]

Have Found the solution...
As mentioned before, I have put a listbox on the form set to visible=no
The rowsource is my Qry_Avail_StockItem query

On the On_Click event of my command button is:-

Dim rs as object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Stock_Item_No] = " & Me.List94.ItemData(0)
Me.Bookmark = rs.Bookmark

Thanks for all your help Tim..

 

[Tim L]

Don,

glad that you got it figured out. I'd just posted a reply to you with the ItemData solution in it and then realised that you'd already got there, well done!

I am a little concerned over your database structure. You seem to have gone to a lot of trouble to create a relational system, for use in a relational database! There may be a good reason for having done this and forgive me for being forward but there may be an alternative way to solve your problem. I've attached a prototype/example stock database illustrating how the relational aspects of Access could be put to use to aid you with your project. There are three tables and one query; tblProducts, tblSizes, tblSaleItems. Relationships have been established between the three tables which I think will mimic what you want, although I don't know the full requirements for your project, so there will no doubt be lots missing; it's just an example.

Regards,

Tim

 

[kappa]

Tim,
I can understand what you are saying about simplifying relational databases and thank you for the sample database you attached.
There are a few things in the sample that would make life a bit easier and I will attempt to check it a bit more in detail. Unfortunately I am a bit restricted in the way I handle certain things due to a proprietary P.O.S. System (and proprietary configuration file structure)..

P.S. Is it ok to send you a PM ? As I am relatively new to this forum, I am not sure if this is proper..

Thanks again for your help Tim.. Appreciated

Don

 

[Tim L]

I can understand a bit more about the restrictions that you are bound by, given the further information.

Glad that I can be of assistance, it is rewarding to know that help provided is both relevant and appreciated.

I've PM'd you.

Regards.

Tim