Combobox linked to Records 1

[RemyS]

Hi All,

I was wondering if there is anyway of linking a combobox to a specific record.

i.e.

I want a form which views records Continuously.
Each Record on this form has several fields.
One of these is going to be an updateable coment field (much like a Call logging system).
I want the user to be able to pull down the combobox to review previous comments, and to be able to select a value in the combo box "Add New" (or click a button) which would bring up an Input box.
This string would then be used to append a new value in the Combobox.

I have never seen how a Call logging system works, nor do I know if this is the best way to do what I want to do.

At present when I place the combo box along side the other textboxes in the detail, the same value is shown, when selected, for each Record. I also asume I need a second indexed table to record multiple comments for the same record.

I don't want the users to have to use a second form to View their comments, though an Inputbox is fine to write new comments.

I realize I may be asking a bit much of Access 97, but if anyone has any ideas they'd be most appreciated.

Thanks in advance,
Rémy Still new to DB's and enjoying learning day by day

 

[damber]

Hi Rémy

Mmmmm... I think I understand what you are asking for.. but is it what you want?

To do the above; here is a fairly simple method which shouldn't take too long..

1) Create a new table for the comments with OneToMany Relationship with master call log table - make sure you create a unique Key for the entry and a link field to the master table, - eg: intID(autonumber), intCallRef, txtComments etc..

2) create an unbound combo control which has its locked property as true, then set its default value to something in the order of "Expand to view comment history...". (You can leave the control as unlocked if you want so that the user can select comments.. but rememeber that this control is unbound and any changes they make will not be updated.. which I don't think you would want anyway !? or if you do then add code to do so.

And finally set it's On Got Focus Event to the following code.. you will need to ammend as appropriate:

private sub MyComboOfComments_GotFocus()

Dim strSQL As String

strSQL = _
"SELECT txtComment FROM tblComments " & _
"WHERE (((intCallRef)=" & Me.intMasterCallRef & ")) " &_
"ORDER BY intID DESC;"   '-to show latest first
         
Me.txtComment.RowSource = strSQL

End sub

3) Add button next to combo box which has similar code behind it:

Dim strMessage As String
Dim strComment As String
Dim strSQL As String

strMessage = "Please enter a new comment to add to this call."
strComment = InputBox(strMessage, "Add New Comment")

'user has nothing to add.. so exit sub
If IsNull(strComment) Or strComment = "" Then
    Exit Sub
End If

strSQL = _
"INSERT INTO tblComments ( intCallRef, txtComment ) " & _
"SELECT " & Me.intMasterCallRef & ", '" & strComment & "';"

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

A question immediately pops to mind though:
- Is this combo box going to hold all the relevant comments the user wants to make in a clear and easy to read way? - ie sizing issue with the width of the box?

If I were you then I would have a 'quick view' form which holds a quick summary of the call log, which can then be drilled down (after click of record required) to a standard single form which contains all the details.. you may not need all the space at the moment, though these things always grow!


If you don't want to create new tables etc then why not try storing the data in a memo field called comments in the master table and separating them by ";" e.g: "Comment1;2;3etc" and add the latest to the front with text manipulation and then set this as the rowsource of the unbound field... only problem is this is quite limiting: for example if the user uses ; then it will cut the comments up when it shouldn't.. BUT this can be overcome with using the chr(34) statement when adding to the front of the field -ie field would contain: "Text1";"text2";"comment3" etc.. all seems a bit messy somehow.. and it is specifically formatted for the combo box - not pretty on reports and all that!

Anyway, enough rambling.. hope it works.

Damian

damber@damber.net

A smile is worth a thousand kind words. So smile, it's easy!

 

[Lightning]

Remy

If you are using a continuous form (as opposed to a datasheet view), why wouldn't a Form/Subform structure work for you? Base the subform on your second table and it will always be visible on each record. While it might be a "Second form for viewing records", as far as the users are concerned it is only one form to search and view the records.

Certainly a lot easier to set up.

Just my $0.02 worth

HTH
Lightning

 

[damber]

Lightning / Rémy,

You can't use subforms within a continuous form type, or activex controls, or graphs and such like.. just an added microsoft feature... :-(

Damian
damber@damber.net

A smile is worth a thousand kind words. So smile, it's easy!

 

[RemyS]

damber,
I applied your suggestions (1-3) to see how they would work in relation to my needs.

Thanks - they probably work better than the method I would have applied.

BUT... and I emphasise this

The combobox still isn't explicit to each individual record.

In the detail section of the Form are bound text boxes for all the fields I want to show. This Form is Continuous, so it shows as many records as I've allowed space for.

I want a combo box to acompany each record and explicitly show the comments for that record.

At present the combobox shows the same value in each instance. I guess it might have something to do with being unbound???

Is there anyway I can link the value of the combobox to the intCallRef of the record?


Still new to DB's and enjoying learning day by day

 

[damber]

Rémy,

This should work.. if the unbound field is in the detail section with the rest of the fields.

When you pass the focus to the control it looks up all the entries in the comments table which have the linked call ref field equal to the currently selected records master call ref..:

Call_1
|_ID_1 - Call_1 - Comment 1
|_ID_2 - Call_1 - Comment 2
Call_2
|_ID_3 - Call_2 - Comment A
|_ID_4 - Call_2 - Comment B

etc, etc,..

Say if you click the combo for Call_1 it looks into the database (using the call ref from within this record) and retrieves all entries in the comments table where the value = Call_1 (2 records) and displays them as the rowsource of the combo box.. this all happens before the combo box is opened so the user should only see the values for this call's comments, and all the other combo boxes for the other records should show the default value.

To try and clarify things.. do you want to do something with the value in that box or do you just want it as a lookup to show a history.. I presumed the latter, maybe this was incorrect?

My thoughts were that you wouldn't want to SET any data to the old comments.. therefore wouldn't want to use the data other than for reference.. If you do then what is it used for?

If you are still unsure then please feel free to elaborate and/or send the db / form & tables etc.

Damian
damber@damber.net

A smile is worth a thousand kind words. So smile, it's easy!

 

[purpleflash]

I know that Microsoft says you can't have subforms associated with a continuous MAIN form.

BUT YOU CAN.

When you create a continuous form and put a continuous subform in it. Access says you can't do this and sets the main form back to single. IF you accept this and then change the MAIN form back to continuous after you've embedded and saved the subform lo and behold you now have a continuous form with associated continuous subforms!

I have built many applications with many embedded continuous subforms associated with a MAIN Subform.

Worth a try!

 

[RemyS]

damber,

The SQL for the RowSource on the GotFocus event works fine.
Equally the Button click event to add a new comment also works great.

The problem was that on my continuous form I'd have, for example, 7 records showing. I wanted each of these records to have its own combobox showing the last comment for that record.

What hapens is; when I look up the last comment, it correctly searches for only those relating to the particular record on which I am searching, but then displays this on all 7 Comboboxes on the form. So they are not exclusive to the record which they should represent.

Solution:
I made the Combobox bound to an extra field I placed in the main Table.
This works and makes the Combobox exclusive to the record. It means that a piece of data is recorded twice in the Comments table, and the Main table, but the later is merely a copy of whichever comment was last selected for that field.

I then transfered the GotFocus SQL code to the Form_Open event (and followed this with a Me.Requery comment for good measure so that Each record would show the most recent comment, if available, from the start,

although I'm still trying to figure a way of forcing the combobox to take the most recent comment;
if, for some reason someone viewed an earlier comment and left it at that, that earlier comment shows up when the form is reloaded because it has been inserted in the table.

I may include a delete Query to remove all comments from the added fields in the main table to get around this (only if I can't figure out a smarter way to do it)

I hope that's clear enough - I've been wracking my head through trial and error of all the possible methods and combinations of code and control settings that I could think of to get this to work.

*:->* Job well done - what fun. Still new to DB's and enjoying learning day by day

 

[RemyS]

I meant to say that I'd include an Update Query not a Delete Query. That's be silly.

Anyway, Thanks for all the advice damber Still new to DB's and enjoying learning day by day