advice re subform and linked table data

[werafa]

Hi all,
I'd appreciate some advice on how to set up a subform.
The subform needs to allow a user to enter a list of team members, and then automatically assign the team name to each subform record

I've got a user form called Team Management. It links to a TeamMembers table, and allows users to set:

Team name
team leader
and team members (via the linked table)


Team member names are set via a combobox/lookup function that is applied to the table.

Is there a way to do this in access, or is this a job for VBA?

thanks
Werafa

[OBP]

The team combobox/lookup function should be reproduced on the subform and that is where the selection should be made, the combo in the table is not necessary.
The combo box control source should be set to the team member field, only the ID should be stored.

[werafa]

I'm still learning how to drive Access - cant figure out how to create the subform combobox.
and don't yet know the kewords to seach for 'how-to's and tutorials etc.

it should be easy based on what I've seen :(
am trying to bypass some linked tables at the moment to see if this is where my problem is

[werafa]

yes it is part of the problem.

I'm now about to fix it by using vba to make a local, independent table from a record subset each time the database is opened.
just need to figure out whether I can run a vba make-table, or whether I deleted and remake the whole lot manually

[werafa]

k - some progress.

can run a maketable sql string in VBA
can set the relationships in vba

tables now show the nested, relational structure in form view.
Access obviously does not much like linked tables (from another bdase file)

I'll finish redesigning this and have another crack at the user form
Werafa

[werafa]

for future reference - the code is:



Option Compare Database
Option Explicit


Public Sub SalesStaffTable()
'make local, stand-alone table of sales staff
Dim myDB As DAO.Database
Dim mySQL As String


Set myDB = CurrentDb()

'create SQL string
mySQL = "SELECT Staff.ID AS StaffID, Staff.FirstName, Staff.Surname, " _
& "[Firstname] & "" "" & [Surname] AS cName, Offices.Location, Organisations.OrgCode " _
& "INTO tblSalesStaff " _
& "FROM Organisations INNER JOIN (Offices INNER JOIN Staff ON Offices.ID = Staff.Office) " _
& "ON (Organisations.ID = Staff.Org) AND (Organisations.ID = Offices.lOrg) " _
& "WHERE (((Staff.HasSalesReporting)=True));"


'run update
DeleteTable ("tblSalesStaff")
myDB.Execute mySQL


'set primary key column
myDB.Execute "CREATE INDEX NewIndex ON tblSalesStaff(StaffID) With PRIMARY"




End Sub


Function DeleteTable(myTable)
'not needed?

On Error GoTo DeleteTable_Err
DoCmd.Close acTable, myTable, acSaveYes
DoCmd.DeleteObject acTable = acDefault, myTable


Exit Function
DeleteTable_Err:
If Err = 7874 Then
Resume Next
Else
MsgBox Error, Err
End If
End Function




Public Sub SetRelationship(primTable As String, secTable As String, primField As String, secField As String)
'set relationship between two tables
' one to many:
' with Referential integrity (on primary key field?)
' cascade update and delete are disabled


Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field
Dim myName As String


Set db = CurrentDb()
myName = primTable & " " & secTable
Set rel = db.CreateRelation(myName) 'Create a new relation.


'Define its properties.
With rel
.Table = primTable 'Specify the primary table.
.ForeignTable = secTable 'Specify the related table.

'Specify attributes for cascading updates and deletes.
'.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade


'Add the fields to the relation.
Set fld = .CreateField(primField) 'Field name in primary table.
fld.ForeignName = secField 'Field name in related table.
.Fields.Append fld 'Append the field.
End With


'Save the newly defined relation to the Relations collection.
db.Relations.Append rel

End Sub

[OBP]

I am extemely impressed with your VBA for creating the table.
My only question is, is it actually necessary to do so each time you open the database?
I am not sure what has led you to that conclusion.

[werafa]

Thanks for the compliment - I'm good on excel, and a rank beginner at access.
I fell into this because the excel systems I was developing got too big.....

I'm attempting to develop several linked subsystems. Staff details are universal - and I've set up one database to hold this. I've got two subsystems now that access data via linked tables - and therefore reduce the number of staff who get to access the more sensitive data. So the info update is designed to create a stand-alone, local replica of the linked data and eliminate the (apparent) limitations of the linked table.

I'm now back at the form I use to set up staff teams.
I've got two linked tables - Teams & TeamMembers.
I can set the team name, and team leader as per normal.
I can set the team members via the linked teammembers table (embedded in the userform)
I'm now looking for an event that I can trigger to catch the record ID and save the team details (TeamID) to the teammember record.

-> am going to have a dig at the table itself to see what I can trigger

[OBP]

The TeamID should be an Autonumber field, which Access Automatically increments and updates for you.

[OBP]

One of the things that you have to understand about Access, unlike Excel, is that it automatically saves any Additions or Record Edits as they are made.
You do not have to arrange anything in that respect.

[werafa]

Noop - have to work it from the user form - have to capture the team name/id to pass to the record.
there has to be a way to do this :(.......

[werafa]

TeamID is autonumber

I have:

#Teams
TeamID [pk]
LeaderID[fk] (show staff name)
TeamName

#TeamMembers
TeamMemberID [pk]
StaffID [fk] (show staff name)
TeamID [fk] (show team name)

I want to:
> open a form for 'team 1' (select or create team 1)
> add team member to the team 1 list
> have each team member recorded as belonging to team 1

someone has to have done this before

[OBP]

I have done this many many times.
The key field for the main form is Linked via thr master/child links of the Main/Sub forms, so when you select a team in the main form the Subform is automatically allocated that TeamID number via the Link.
The team memebers can be chosen using a combo box selecting them from the Personnel table.

[OBP]

See the attached database for a simple example.

Click on the worklog button.
ps I no longer use Menus.

[werafa]

ummm. I think I shall cry.....
I expanded the column that had gotten hidden somehow and guess what I found.......

have been nutting this out since early this morning. Bleep, Bleep, Bleepbleep bleep.
ah well, that is what I get for being a new south welshman with murphy for a name.

I did one hear murphy's law for combat ops.
"No plan survives contact with the enemy, because:
> incoming fire has right of way
> and your weapon was made by the lowest bidder"

Thanks for pointing this out

[OBP]

So are you all good now?
You have to try not to think in Excel terms when working with Access (I work with both) and treat Access as what it is a "relational database", so it does most things automatically regarding data, but has to be set up to do so.
A good old fashioned book on Access is a real advantage, it would be cheap and wouldn't have the latest bells & whistles but it would be great for basic design stuff.

[werafa]

I'm good until I start the next step :):crying:

I also suspect that your donation is worth dissecting too

[OBP]

I can supply some more advanced ones if you want, they have more multiple tabbed forms/subforms and VBA.

[werafa]

lessons are always welcome. (yes, I'd appreciate a look)

my first user form was in excel, and was made in an attempt to get an amateur spreadsheet designer to stop making pretty spreadsheets - and made good use of an auto-expanding suite of tabs. Access form design is easy by comparison - I just have to get used to the access object libraries and the design headspace.

I'll trade you an excel workbook that automates pivot tables (by VBA) if this is useful

Werafa

[werafa]

ps, why do you not use menus any more, and what do you use?

[OBP]

Thanks for the offer, but I retired 13 years ago, I just do this forum stuff to keep my brain active.

I no longer use menus because they slow doiwn the users, I use multiple tabbed forms, I will post one, it has no data as it was very sensitive.

The one that I wanted to show you is too large, it is 8.4Mb zipped, so I have taken a print screen of it instead.