How to find cross table values

[mnms]

Hi, I've been looking through these pages and the net looking for an answer to my current problem - to no avail. I'm working on an access database, and triggering the on change event on a form I'm trying to figure out how to write this in VB.

I have two tables and a form.
Table one: value 1, more data
Table two: value 1, value 2, more data
Form: Field value 1, value 2

On my form I'm trying to enable, by choosing a value 1 from a combobox, based on table one, to retrieve value 2 and displaying it in a textbox on the same form.

I've tried several differnet options and have come to the conclusion that I must be looking at this the wrong way. It ought to be simple - but I just can't seem to nail it.

Please help me :)

[Dököll]

Hi, I've been looking through these pages and the net looking for an answer to my current problem - to no avail. I'm working on an access database, and triggering the on change event on a form I'm trying to figure out how to write this in VB.

I have two tables and a form.
Table one: value 1, more data
Table two: value 1, value 2, more data
Form: Field value 1, value 2

On my form I'm trying to enable, by choosing a value 1 from a combobox, based on table one, to retrieve value 2 and displaying it in a textbox on the same form.

I've tried several differnet options and have come to the conclusion that I must be looking at this the wrong way. It ought to be simple - but I just can't seem to nail it.

Please help me :)

Hello, mnms!

Please do not take this the wrong way, I am slightly confused, as I have read this a couple of times:

(1) Is it my understanding you now need to build VB forms instead of MS Access forms

Or

(2) Will you keep the Access data table(s) and interact using VB forms

If you need to play aroung with combo boxes in VB, you may not need MS access, you can use a text file.

Please continue to ask questions, and it might be helpful to include what you have working thus far to see where you are.

Good luck and welcome!

Dököll

[mnms]

Hi Dököll,

Thanks for taking the time to answer me. I had hoped to avoid showing any code as I'm pretty sure it's faulty and not certain if that is the way to do it. And I'm sorry if I was too vague in my first description.
I'm working on an Access database and I'm using Access forms. I did wonder if I should have posted in the access forum but this seemed more appropriate. Anyway, back to the problem.
My database handles spent hours on projects (type designator). I haven't found out how to calculate the hours spent yet, so I know that part is faulty, I was focusing on how on earth to show any of the hours stored in the tblLog table on the current form, before I'd worry about the calculations. The Hours in tblLog is a time field calculated using the Timediff function. I've been using Access 2003 VBA as an example so if you recognize some naming from there there's a reason :).
Anyway the code in question looks like this so far:

Private Sub fTypeDesignator_AfterUpdate()
'calculate the total spent hours used for the chosen project
If IsNull(Me.fTypeDesignator) Then
'do nothing
Exit Sub
Else

Dim rsContacts As ADODB.Recordset

'create new instance of a recordset
Set rsContacts = New ADODB.Recordset
'set various properties of the recordset
'With rsContacts
.CursorType = adOpenStatic
'only read-rights
.LockType = adLockReadOnly
'open the recordset based on tblContacts table using the existing connection
.Open "tblLog", CurrentProject.Connection

'This is the part I wanted help with:

.Open "SELECT [Hours] FROM TblLog WHERE [Type designator] = "" & Me.fTypeDesignator""", CurrentProject.Connection

'end of the part I wanted help with

End With
Do While Not rsContacts.EOF
spentHours = spentHours + rsContacts!Hours
rsContacts.MoveNext
Loop
'yes I know this isn't right:
spentHours = SUM(rsContacts)
'insert into fSpentHours on the form
Me.fSpentHours = spentHours
'close the recordset
rsContacts.Close
'set the recordset and connection to nothing
Set rsContacts = Nothing
End If

End Sub

I hope it was more clear this time.

Br,

MnMs

[mnms]

Heh - I found it out myself - I just needed to bang my head against the wall several times!
The solution was instead of a recordset to use DLookup()

But thanks again for taking the time to look at it - it was very much appreciated! :D