How can I refer to individual records on a tabular subform

[john-86]

Hello all,

I've been searching for a solution to this problem for a long time now. I've looked through the forums and through all the help files and the answer still illudes me. I believe I've even posted a message like this a while ago (can be deleted admin. Sorry for the duplication - I have a tight deadline to meet - I'm sure you understand). Can anyone help?

Below is a screengrab the form frmContexts with a tabular subform embedded in it called frmStratigraphy.

http://i57.photobucket.com/albums/g218/johngregson483/frmContexts.jpg


Before I save a record I must check that the context referred to in the subform exists in the recordset (as to not produce orphaned records). Then I must code the reciprocal relationship so that, for example, if I created a link from C3 to C4 - a reciprocal link would have to be created from C4 to C3 with the opposite "relationship" term.

Anyhow, what I'm looking for is a way to select which record is being pointed at since all the control names will be the same. I've wondered whether there was a records collection so that I could do a for each - next loop i.e.


For Each record In recordset

dim contextFound As Boolean
contextFound = False

Do
Do While Not contextRS.EOF

If Forms("frmStratigraphy").txtRelatedContextID.Value = contextRS.Fields("contextID").Value Then
contextFound = True
Exit Do
Else
contextRS.MoveNext
End If

Loop
Loop Until contextRS.EOF

if contextFound = False Then
MsgBox("Context with ID " & forms("frmStratigraphy").txtRelatedContextID.Value & " was not found. There must be a context with this ID already on the database.")
Exit Sub
End If

Next


But there's not records collection. Maybe the above code will indicate better where I'm trying to go with this. If further explanation is needed, just let me know.

Thanks everyone:hi: ,

John

[Norie]

John

Can you explain how the data is structured?

[john-86]

Hi Norie :) ,

I have three tables for this:

tblContext - houses all data from form with the exceptions of the Area field and Stratigraphy subform fields.

tblContextArea - houses the Area field data. Table has two fields - contextID & contextArea. Each area (given by a letter) is given its own record to make searching easier (I realise now I could've maybe done this with the InStr method instead).

tblContextStratigraphy - houses data from stratigraphy subform. Table has four fields - contextID (same as on frmContexts), relatedContextID, relationship (e.g. "found above") & reciprocalRelationship (e.g. in the case of relationship being "found above" reciprocalRelationship would equal "found below" - the opposite). contextID & reciprocalRelationship are filled by VBA, the other two fields filled by the user. reciprocalRelationship is dependant on another table which is edited using the 'Edit Reciprocal Relationships' button on frmStratigraphy.

I hope I'm not just overcomplicating things.... I think I may have found a way forward. I'm just trying it now.

I have till thurs morning to have this db finished :nervous:.

Thanks & regards,

John

[OBP]

Is this some kind of course work?
As I can't see the point of editing relationships.
If the master child links are correctly set the data willcorrectly linked.

[john-86]

Hi OBP,

this is actually a database I'm creating for a group of archaeologists from Denmark. They're coming to do an excavation for my company. The reason I've added the capability to add and edit relationships is because it's a little difficult to predict every kind of spatial relationship there could be between two contexts. I thought this way the user could add as he/she needed.

Forgive me, I am fairly new to all this. This is my first database using completely unbound forms. I'm not a database expert, I'm a university student studying from home and working full-time. If there are any flaws in my logic I'd appreciate them being pointed out to me. This is something I'd like to get better at. Goodness knows why - ICT has got to be one of the most stressful job-types :banghead: ... everyone agree?

Regards,

John

[OBP]

John, can you post a zipped copy of the database on here for us to look at?
Why are the forms "unbound", what is the need for that?

[john-86]

Hi OBP,

my only "training" in VBA was a tutorial CD from the Virtual Training Company (www.vtc.com (http://www.vtc.com)). The guy who does the tutorial for VBA (a chap called Mike Haines) says that he always uses unbound forms in his applications as it gives a far greater control of the data.

Even though I'm fairly new to all this I can see the benefits although it is a much more complicated & time-consuming process.

John

[OBP]

John, it is not my place to contradict someone who makes a living using Access, but I do not think it is necessary to use unbound forms all the time, I do use them for searching and filtering though.
It makes for a completely unnecessary amount of work to do what Access does for you so easily and quickly.

[Norie]

John

I agree with OBP.

I've seen a lot of people using unbound forms and then code to write data to tables.

To me that just defeats the purpose of a database.

Obviously in certain circumstances you might need to use unbound forms/controls but if you set things up correctly you should only need to use them minimally.

Sorry for not answering your specific question, I'm still trying to get my head round the table structure.:bug:

[john-86]

Hi guys,

I appreciate your advice. I do see where you're both coming from. It certainly hasn't made my job any easier. One form has 28 A4 pages of code :wot. Next time I go to do a database I'm going to seriously question the benefits of unbound forms.

I think with a good UI they can be a great idea. It, as I've said, does give you absolute control over what happens. It would seem, however, that it (as everything else) has its place.

Regards,

John