Showing select information

[lithium]

Okay I'm working on a small issues database and need a few pointers since I'm new to access (but not programming and database). I only created one table where all info is store (people request something to be fixed and when its fixed, the department will change status to resolved). What is the best method to display the items from the database marked as "active"? What window would you use and how would you query for it thru access?

[ItsMe]

I use yes/no fields in my tables to distinguish the status of a given row. I make it a habit to assign the default value of either 0 or -1 to the field at the table level. I do this to avoid issues of Null fields when I want to check on the status. By assigning a default value to the field, I am assured the field will be either zero or negative one, where -1 is Yes aka True

[June7]

Build a form as GUI for data entry/edit and a report to output filtered records.

[lithium]

I got it to work and load into a listbox (works great for what I need). My other issue is that I need to figure out to refresh the listbox once an addition as occurred. The big issue is that I created a new form for the user to add an entry and when I exit the form I don't know what code to use in order to refresh the listbox which is on my main form?

And also, I wanted the user to be able to highlight a record from the listbox and click a command button (Edit) and be able to have another form load with the precise data they highlighted and be able to change that record...I'm new to this so it's a learning curve but I have familiarity with VB and Java so I'm not totally new to coding in general.

[June7]

What is the code that opens the add record form?

[lithium]

I just used a macro event to open form on the command button

[June7]

I only use VBA but maybe macro has equivalent.

Open the add record form in dialog mode. This suspends code execution in the calling procedure until the add record form is closed. Then the next line of code is to requery the listbox.

DoCmd.OpenForm "formname", , , , , acDialog
Me.listboxname.Requery

Use code in the listbox AfterUpdate event to open form filtered to the item selected in listbox, something like.

DoCmd.OpenForm "formname", , , "ID=" & Me.listboxname.

BTW, if the listbox is bound, should not use it to enter search criteria because that will change the value in record. Use unbound controls for user selection of filter criteria.

[lithium]

Thanks i will give that a try. Looks like I'm gonna have to brush of some rust and do coding instead of these macros to accomplish my goal

[lithium]

It worked with everything except loading the data into the other form (when they go to edit). I put the DoCmd.open form code under the double click event and need the information (title, assigned to, assigned by, status, and priority ) to populate the text boxes on that form but they appear blank

[ItsMe]

You can use OpenArgs or you can reference the controls on another form using VBA, so long as the other form is still open. What is the code you ended up using? I am a little lost in understanding what you have tried and where this information is supposed to appear. Perhaps a description of where this data came from; the data you want to populate the other fields with.

[lithium]

I have everything going except the edit portion. I have two forms: Main and Add. When the user clicks the add button on the main form it has a popup for the Add form. I basically want the user to be able to select one of the records fromthe list box on the main form and once they double click it, my new form (edit) will appear with the data they selected in the list box on main.

[ItsMe]

.....my new form (edit) will appear with the data they selected in the list box on main.

So you have only two forms? The "edit" form is your Add form? Seeing the code often times answers these questions.

In your Add form, the popup that is being used for editing purposes, you can reference the listbox value. Since you are using DoubleCLick event handler, I will assume this is not a multiselct listbox.

Forms![MainFormName]!
[ListboxControlName].Column(0)

so you can assign the value to your Add form something like this.

Me.Assigned_To.Value = Forms![MainFormName]!
[ListboxControlName].Column(0)

You need to get the column index correct to match the different fields in your edit form.

You can do something like this, in your Add form's load event, to make sure you have the correct column indexes. View the results in your Immediate Window. You can use Ctrl+G to display the Immediate window in your VBA IDE.

Debug.Print "Collumn 0 = " & Forms![MainFormName]!
[ListboxControlName].Column(0)
Debug.Print "Collumn 1 = " & Forms![MainFormName]!
[ListboxControlName].Column(1)
Debug.Print "Collumn 2 = " & Forms![MainFormName]!
[ListboxControlName].Column(2)
Debug.Print "Collumn 3 = " & Forms![MainFormName]!
[ListboxControlName].Column(3)

[ItsMe]

For some reason I can't get the formatting to cooperate in the previous post. Maybe something like this.

Debug.Print "Collumn 0 = " & Forms![MainFormName].ListboxControlName.Column(0)
Debug.Print "Collumn 1 = " & Forms![MainFormName].ListboxControlName.Column(1)
Debug.Print "Collumn 2 = " & Forms![MainFormName].ListboxControlName.Column(2)
Debug.Print "Collumn 3 = " & Forms![MainFormName].ListboxControlName.Column(3)

[June7]

Column has only one 'l'.

Apparently the listbox is not used for purpose I thought.

I wonder why data is duplicated. Why do you need all the values from the listbox? Why not saving just ID that represents the record selected in the listbox?

If you want to provide db for analysis, follow instructions at bottom of my post. Identify object involved in issue.

[ItsMe]

Thanks June. I seem to be having difficulty typing this morning. And I was wondering the same thing about duplicate data too?