Form/Table interaction Help

[KyleT]

Hello,
I am currently *trying* to create a pretty simple DB which will allow the company which I work for to keep a table containing a list of clients, a table containing a list of services, and to be able to link between the two.

I've setup 3 tables, a Client table, Service table and a Link Table.

Each Client has a numerical ID, each service has a Numerical ID, and each Link has a Numerical ID - so the link table may look like:

Link......Client....Service
1.........1..........1
2.........1..........2
3.........1..........3
4.........2..........2
5.........2..........3

The above table basically contains data for 2 clients, Client1 and Client2. Client1 recieves Service1, Service2 and Service3 - wheras Client2 only recieves Service2 and Service3.

I hope I'm making sense!

Basically I want end users to be able to add/remove services from a client using a form. I have created a basic form with a combo box and command button - The user selects a client, then hits the button - the button runs a query to bring up all the services for that client, and opens up a new form.

The new form will have 2 listboxes. The left one will contain every service available, fed directly from the Services table. The Right listbox will contain a list of services currently linked to that client as per the link table.

I have no issues populating the lists, however the next stage will be to add 2 command buttons, which will allow the user to either: Link a new service to the client (this function needs to create a record in the link table with all the relevant IDs, and also populate the right hand listbox with the new service) or to delete a service from a client (this will need to delete the relevant link from the link table, and remove the service from the right hand listbox).

I really don't know how to achieve this, unfortunately it is beyond my very basic knowledge of working with access.

Hopefully I've made sense, and someone can help me. If not - nevermind

 

[systemx]

Hi KyleT,

Adding the service is quite simple. Search on either ADO or DAO methods for adding records in this forum. You will find heaps of examples of how to add a record to a table.

Something like -

Dim ADOrs As ADODB.Recordset

Set ADOrs = New ADODB.Recordset
ADOrs.ActiveConnection = CurrentProject.Connection
ADOrs.Open "tblLink", , adOpenKeyset, adLockOptimistic, adCmdTable

With ADOrs

.AddNew
.fields("Client") = Client Number
.fields("Service") = Service Number
.Update
.Close
End With

You can also use .Delete to delete a record from a table.

Cheers,

Rob



Set ADOrs = Nothing

 

[KyleT]

Hi KyleT,

Adding the service is quite simple. Search on either ADO or DAO methods for adding records in this forum. You will find heaps of examples of how to add a record to a table.

Something like -

Dim ADOrs As ADODB.Recordset

Set ADOrs = New ADODB.Recordset
ADOrs.ActiveConnection = CurrentProject.Connection
ADOrs.Open "tblLink", , adOpenKeyset, adLockOptimistic, adCmdTable

With ADOrs

.AddNew
.fields("Client") = Client Number
.fields("Service") = Service Number
.Update
.Close
End With

You can also use .Delete to delete a record from a table.

Cheers,

Rob



Set ADOrs = Nothing

Very Good cheers, i'll have a play with this shortly. I knew it would be fairly simple, just needed a push in the right direction.

Regards

 

[KyleT]

Can I use the ADOrs.Open "QueryName" to open a query?

As the record I need to find, will be obtained by a query..

Hope im making sense!