picture display based on combo entry

[Renoir]

Hi, is there a way of showing a picture based on the content of a combo box? Answer - Yes. The problem is that the picture is stored as a field in my "tblFileStatus" table (the status is either IN or OUT and the picture just displays this). This information is then displayed in a combo box in a form which has the source data come from a table other than tblStatus.
I have an afterupdate event procedure in the combo box as such:
Private Sub StatusID_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Picture] = " & Str(Nz(Me![tblFileStatus], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
1. how do I configure the source data in the bound object to link it to the picture?
2. is the above sub correct?

Any ideas???

Thanks in advance.

Renoir

 

[Luddite Lad]

This thread might give you some pointers. Check out John's changes to the DB.

 

[Renoir]

Almost

Cheers,

I’m a bit wiser again.
However, I think I have a specific problem based on the combo the pictures rely on.
The combo gets its info from 2 tables and I think this is why it doesn’t work.
I have enclosed the sample to illustrate. Just open the “Asset” form.
Any ideas what the criteria in the code needs to be other than “In” or “Out”?

Renoir

 

[John Big Booty]

Just having a very quick look; I think you need to base your test on the StatusID 1 for "IN" and 2 for "Out", I'm not sure what you want to do if the StatusID is 3 or 4 though.

You might also want to put the test in the on current event for the form as well.

 

[Renoir]

Thanks for replying.
Looked at your original post and learned a lot.

The "on current event" is noted - I make the "After Update" work first.
I think the problem I'm having stems from the fact that the combo box is getting its value from tblAssets which in turn gets its value from tblFileStatus.
The combo's data:
SELECT DISTINCTROW tblFileStatus.* FROM tblFileStatus ORDER BY tblFileStatus.Status;
Is this the reason that on "After Update" I loose the images - it doesn't see "In" or "Out" as valid values in the combo?
I'm confusing myself here - hope it's clear.

Renoir

 

[John Big Booty]

If you change the code to the following it should work fine.

If StatusID.Value = 1 Then
        ImageIn.Visible = True
        ImageOut.Visible = False
    ElseIf StatusID.Value = 2 Then
        ImageIn.Visible = False
        ImageOut.Visible = True
    Else
        ImageIn.Visible = False
        ImageOut.Visible = False
    End If

 

[Renoir]

My hat off to you Sir - works like a dream.
I only have about another 400 other little issues to battle.
We will carry on...

 

[Renoir]

My next hurdle…

As you would have seen from the example, I’m trying to tweek the MS standard template to make the asset tracking DB into a paper file tracking DB.

My next step is to somehow create a subform in the “Asset” form under the tab “File History” that will display who had the file, for how long and when it was returned (if returned).

Similarly, in the same form under the tab Owner Status” I need to display all the other files that are currently owned by the File Owner displayed in the “File Owner” combo on the main form.

Could you steer me in the right direction to solve these issues. I bundled them because I assumed they are simular in nature.

I have again attached the DB in case my explanation sucks.

As you would have noticed, I’m a beginner at all this so generalities do not assist me much. On the other hand, I want to learn and understand the solutions offered here in this forum so I’m not asking for tailored solutions.

Your help is very much appreciated.

Cheers,

Renoir

 

[John Big Booty]

The first thing you are going to need to do is correct the way you are using the Combo Boxes and storing the the data derived from them.

Currently they are set up so that you are storing the data in the visible column rather than the hidden column.

Your Assets form is bound to the Assets Table (you might like to consider naming it something like TBL_Assets [and all other tables for that matter, try using prefixes such as TBL, QRY, FRM, RPT], so that it is apparent that it is in fact a table rather than a query or something else). The table has a field called Employee ID (good) however the data it is storing is the employee name rather than the ID. This because the bound column of the Combo Box is Column 1 rather than Column 0 (the first column is labeled as 0 the next 1 etc.).

Have a look at how all your Combo Boxes are working and fix that, then we can look at the next step.

 

[Renoir]

Thanks for your reply (again)

Small but exciting win
On a bright note, I have the subform working on the “Other Files” tab in frmAssets that displays all other files owned by the employee ID on the main form (the wizard can take the credit for that one). I based it on “qryOtherFiles”

Prefixes
I take your point about the prefix methodology. I changed all the names to include the prefixes however some of the “Reports” macros do not work anymore (others I cound fix). That doesn’t matter as I have to redo the report structure later anyway. I stopped at changing prefixes for buttons, labels and combos etc…

Combos
I think (correct me if I’m wrong) that the combos are based on a lookup function in the tables themselves. When I look for example at the EmployeeID field in tblAssets I see the following:

SELECT [EmployeeID], [LastName] & ", " & [FirstName] FROM tblEmployees ORDER BY [LastName] & ", " & [FirstName];

With “Column Count” set to 2, this is then displayed in the “Column Widths” as: 0cm;5.08cm

Is this a problem? I assume this is set up for easy data entry in the tables forcing users to pick rather than free hand the entries in order to enable quantitative reporting.

I did play around with these settings but no combination displayed the correct info in the table and/or forms.

Not sure where to go from here on the combo box issue…

 

[John Big Booty]

Good work on sorting out the subform for Other files.

Now that you have added Prefixes to your table names (etc) you will need to re-work your macros to reflect these changes, then they will work.

I've had another look at your combo Boxes, and now understand what is happening. The lookup tab on the field properties in the table design have been set to combo box. This means that when you drag and drop the field name during form design those fields will default to a combo box. So the data is in fact being stored correctly (however it displays as it will display in the combo box). My apologies for sending you on a wild goose chase If you do not want this to happen simply change the lookup to textbox, although bear in mind that should you wish to revert the combobox default you will need to rework it from scratch.

Now to get the files history; you will need to create a new table to store the fileID along with who has held it and it's in and out dates, (the time the file has been held can be calculated so does not need to [should not] be stored)

 

[John Big Booty]

You might like to read BobLarson's views on using the combobox on the table look up.

 

[Renoir]

Next and final step...

Thanks again for your help. I must say that without it I would be up a shit creek without a paddle.

I went thought the discussion on "combos in tables" and all I can say is that it does not seem to be a good idea. Once I have mastered the principle issues driving this DB I will redesign it and implement the suggestions offered in the thread.

Talking about principle issues, I think I reach it in relation to finding a solution to storing the lending history of the files. I have created a tblLendingRecord as suggested containing the following fields:
LendingID (unique key)
AssetID (unique identifier for each file in tblAssets)
AssetDescription (file number)
EmployeeID (employer last and first name)
DateSold (the lending date)
DateAcquired (the return date)

I have also created the following 1 to many relationship:
tblAssets[AssetID] to tblLendingRecord [AssetID]
I'm not at all clear as to the legitemisy of the relationship

I then created a subform which nicely displays the content of tblLendingRecord based on the main forms AssetID. In other words, if AssetID 988-1-1111-11 is displayed in the main form then only the history of 988-1-1111-11 is displayed in the subform.

However, this last statement is not all together true because there is no data in tblLendingRecord. I have absolutely no idea as how to populate tblLendingRecord. I looked at loads of threads and searched for any keywords I could think of - to no avail...

I have created a button in the "Task" space to the right of frmAssets labelled "Change Ownership". The functionality is the missing link that stands between me feeling pretty good about my new found knowledge and DB and looking at a dysfunctional set of forms and tables.

I did manage to add a little calendar to the in/out date fields in the main form

Any ideas as to the population of tblLendingRecord?

BTW, I will have a couple of cold once ready if you ever travel through Seychelles.

 

[John Big Booty]

.......

LendingID (unique key)
AssetID (unique identifier for each file in tblAssets)
AssetDescription (file number)
EmployeeID (employer last and first name)
DateSold (the lending date)
DateAcquired (the return date)

.........

So far I've not had a look at the attached DB (I'll do that when I get a chance) however in the above, the info you store in the EmployeeID should be the Employee ID rather than the Employee last and first name.

Should I ever have the opportunity to travel to the Seychelles, I'd be more than happy to share a beer with you

 

[Luddite Lad]

The way I would approach the lending history; Is firstly I have a form that shows all the files that are available for lending (ie. they have an "IN" status) this you would populate with a select query. The form would have provision to select the file/s that are to be put on loan. This information along with the borrower and date out I would then transfer to your lending history table using an append query.

To return the file I would simply reverse the process. Although you will need to use an update query (rather than an append query) to add the returned date.

 

[Renoir]

Trust you had a good w'end - keep that VB flowing - the liquid variety that is...

I created qtyLendingRecord (Append Query) trying to drag some info from tblAssets to tblLendingRecord - that is the plan in any case.

When I run the query, it tells me that 0 records will be changed. In other words, tblLendingRecord is not being appended.

Is this fixable in the query's "criteria" or do I search somewhere else for the answer? Obviously, I'm trying to only append the current record displayed in frmAssets

Once just running the query works OK, I figure that I can assign a macro to the "Change Ownership" button. Am I on the right track with this approach?

Thanks in advance for your kind words of wisdom.

Renoir

 

[John Big Booty]

Mornings Renoir

In your query to select the current record put the following in the criteria for AssetID

[forms]![frmAssets]![AssetID]

That will select the current record that is displayed on your form.

Secondly remove the destination table from the upper section of the query pane.

As a tip; were possible construct any query as a select query and then change it to the type of query you want once you are sure it is picking up the data you want it to.

I'm currently having a bit of a play with your DB, and will post my changes latter in the day.

 

[John Big Booty]

Renoir

I've tweaked your DB a little. I've added a button to put a specific file on loan and another to return it. I've disabled the Status Combo to ensure that the buttons are used to properly record a files status.

I hope that's headed in the direction you were hoping

 

[Renoir]

Well what can I say?
I had a quick play with it and it looks magnificent!

I will start looking at the niggly bits now and see if I can beat them down – reporting, splitting database, access levels etc…

Thanks again for all your input to date – I would be pretty stuffed without it.