Display Data on Screen & Interact with Data 1

[edutch]

I have done this with other database applications, but I can't figure out how to make Access do this. I want to display one field, unique id nos., from a Table based on a certain set of criteria. The numbers will run from screen top to bottom, col 1, top to bottom, col 2, etc. I then need an input box to call up one of the id no.s displayed. When the id no. is called up, a form would be called in order to update certain data, a marker will be changed and a refresh of the data on the screen will occur. This will continue until all id no.s have been called and updated leaving nothing to be displayed.

I would think a macro would do that could be looped based on a condition, but that skill in Access is beyond me. Nor can I figure out how to define screen placement of each id no. Can anyone help me with this?

 

[fneily]

I have no idea what you mean by calling up one of the ID's. I would do this: create a regular form based on the table. In the form's header section, create a combobox (dropdown) with the ID's selected from a query. When the user clicks an ID, the form shows the info. This is very basic and standard way to filter a form. Any book has it and a search of the Access forms forum probably has example of it. Then after the user updates the table(Saves), code can then delete that entry from the combobox recordsource. Eventually, it'll be blank.

 

[edutch]

Thanks for the idea. At the present I am running a query for that selection criteria, I update an individual record and I re-run the query, which drops out the updated record. Using the drop down menu will definately make that process a little smoother. I had thought of that, but I did not know how the updated record would be dropped from the query, i.e. eliminated from the drop down menu.

What I meant by "id no." is simply a field I have named to hold the registration number of the applications I have input. This database is for a baseball league that holds a pretty large draft for three different age divisions. It would be beneficial to display all of the open "id no.s" on the screen at once in order to know quickly if a particular number is available. Thanks again.

 

[fneily]

If you use the dropdown filter, here's how to delete the selected entry.
To delete entries from combobox. First create a query that just picks off the field you want in your combobox. Test it to make sure you have all your data. Then convert it to a make table query. Now you have a table with just the list you want.
Make a macro. Under the Actions column heading, you'll have Echo, SetWarnings, OpenQuery, Echo, Setwarnings in that order. The first Echo and Setwarnings will be set to No. Then second set will reset them to Yes. The OpenQuery Arguments will be the name of the make table query, then Datasheet and Data Mode will be edit.

On the OnOpen event of your form, click in the box next to OnOpen, click the square with the three dots, select Code Builder and put in the code:
Private Sub Form_Open(Cancel As Integer)
DoCmd.RunMacro "YourMakeTableQueryName"
Me.[ComboboxName].RowSource = "select distinct [FieldYouWant] from [TheNameOfTheNewTable]"
End Sub
So when your form opens, it'll run the make table to get a full list of values and then use that table as your combobox's rowsource.

On the AfterUpdate of your combobox, you'll put:
Private Sub Combob_AfterUpdate()
Dim R As DAO.Recordset
Set R = CurrentDb.OpenRecordset("Select [FieldName] From [TheNameOfTheNewTable] Where [FieldName] = " & Chr(34) & Me![ComboboxName].Value & Chr(34))
R.Delete
R.Close
Me![ComboboxName].Requery
End Sub
So, Access will take what's in the combobox, look in your new table and delete the entry. It'll then refresh/requery the source of the combobox which will now not show the old selection.
The code to show the selected field's info on the form will go after the Requery statement.

 

[edutch]

Thanks again. I am going to try and incorporate your suggestions this weekend. I will let you know how it comes out. Thanks.