Add records to table from Datsheet

[Allan Skyner]

Using Access 2000.
I have 3 tables;
Suppliers (listing all supplier details with unique SupplierID).
Products (2 columns listing ProductID and ProductName)
Contract (3 columns listing unique ContractID, SupplierID and ProductID)

On my main form showing all details per supplier from suppliers table and
listing products supplied by supplier in a list box.

Currently there is a command button on my form1 that opens another form2 to
add a product to a supplier using a text box to enter the 8 digit ProductID
(if known). If not known, on form2 there is a command button that opens
another form3 which enables users to free text search the
products.productname field and the results are displayed in form4 in
datasheet view. This allows the user to find and read the correct ProductID
and then close form4 & form3 and then enter the right ProductID using form2.

This is a lot of command buttons and forms which I am trying to reduce,
merge or simplify. My plan is to merge the functionalities from form2 & 3
into one, which I can safely manage.

What I would like assistance with, if possible, is that when a user does a
search which opens the resultsform in datasheet view (2 columns ProductID,
ProductName) I want them to have the ability to double-click the right
ProductID and this adds that code to that supplier (i.e. the SupplierID and
ProductID are added to the Contract table).

It is more likely that there will be several products added from a single
search, this is why I would like to simplify operations so users can simply
scroll down the list double-clicking the ProductID's that each supplier
supplies.

I'm sure this can be done using the double-click event procedure, I'm
getting a bit lost with the code to complete the task.

Sorry this is a bit long-winded and I thank anyone in advance of some
assistance with the code or some pointers in the right direction.

Best regards

Allan

 

[Michael H]

Hi.

I can't say this is necessarily the best way to do it, but one solution is
to put code similar to the below into the ListBox's DoubleClick event:

Private Sub ListBoxName_DblClick(Cancel As Integer)
Dim strSQL As String

strSQL = "INSERT INTO Contract([SupplierID], [ProductID]) VALUES (" _
& Me.ListBoxName.Column(0) & ", " _
& Me.ListBoxName.Column(1) & ")"

CurrentDb.Execute strSQL, dbFailOnError
End Sub


A few points of interest:
- The code assumes the ContractID field in the Contract table is an
AutoNumber field
- The code assumes the SupplierID and ProductID fields are numeric
- The code assumes the values you want to insert into these fields are
contained in columns 0 and 1 of the ListBox
- There is currently no error checking
- You will probably want to add some sort of a confirmation (a MessageBox
after the CurrentDb.Execute line, perhaps), otherwise the user may be unaware
that anything has happened after double-clicking on the ListBox.
- If your forms display any controls bound to the Contract table, you will
probably want to add code after the CurrentDb.Execute line that requeries
those controls.

Please be aware that the above is untested, and there may be other important
info I am omitting, but this will hopefully get you started. Feel free to
post back with any problems or questions.

-Michael

 

[Allan Skyner]

Hi Michael

Thanks for your reply, which has given me good food for thought.

Further to your assumptions the values which I'm inserting into the
Contracts table are ContractID [autonumber for this table]; SupplierID
[autonumber from Suppliers table]; ProductID [8 digit standard number]. The
ContractID will self generate when new record added, the SupplierID will come
from the form displaying the supplier record (form1) and the ProductID comes
from a form that displays search results in datasheet view (2 textbox
controls showing ProductID and ProductName).

I am trying to adapt your suggestion but any further help you can give will
be greatly appreciated.

Allan

Hi.

I can't say this is necessarily the best way to do it, but one solution is
to put code similar to the below into the ListBox's DoubleClick event:

Private Sub ListBoxName_DblClick(Cancel As Integer)
Dim strSQL As String

strSQL = "INSERT INTO Contract([SupplierID], [ProductID]) VALUES (" _
& Me.ListBoxName.Column(0) & ", " _
& Me.ListBoxName.Column(1) & ")"

CurrentDb.Execute strSQL, dbFailOnError
End Sub


A few points of interest:
- The code assumes the ContractID field in the Contract table is an
AutoNumber field
- The code assumes the SupplierID and ProductID fields are numeric
- The code assumes the values you want to insert into these fields are
contained in columns 0 and 1 of the ListBox
- There is currently no error checking
- You will probably want to add some sort of a confirmation (a MessageBox
after the CurrentDb.Execute line, perhaps), otherwise the user may be unaware
that anything has happened after double-clicking on the ListBox.
- If your forms display any controls bound to the Contract table, you will
probably want to add code after the CurrentDb.Execute line that requeries
those controls.

Please be aware that the above is untested, and there may be other important
info I am omitting, but this will hopefully get you started. Feel free to
post back with any problems or questions.

-Michael

Using Access 2000.
I have 3 tables;
Suppliers (listing all supplier details with unique SupplierID).
Products (2 columns listing ProductID and ProductName)
Contract (3 columns listing unique ContractID, SupplierID and ProductID)

On my main form showing all details per supplier from suppliers table and
listing products supplied by supplier in a list box.

Currently there is a command button on my form1 that opens another form2 to
add a product to a supplier using a text box to enter the 8 digit ProductID
(if known). If not known, on form2 there is a command button that opens
another form3 which enables users to free text search the
products.productname field and the results are displayed in form4 in
datasheet view. This allows the user to find and read the correct ProductID
and then close form4 & form3 and then enter the right ProductID using form2.

This is a lot of command buttons and forms which I am trying to reduce,
merge or simplify. My plan is to merge the functionalities from form2 & 3
into one, which I can safely manage.

What I would like assistance with, if possible, is that when a user does a
search which opens the resultsform in datasheet view (2 columns ProductID,
ProductName) I want them to have the ability to double-click the right
ProductID and this adds that code to that supplier (i.e. the SupplierID and
ProductID are added to the Contract table).

It is more likely that there will be several products added from a single
search, this is why I would like to simplify operations so users can simply
scroll down the list double-clicking the ProductID's that each supplier
supplies.

I'm sure this can be done using the double-click event procedure, I'm
getting a bit lost with the code to complete the task.

Sorry this is a bit long-winded and I thank anyone in advance of some
assistance with the code or some pointers in the right direction.

Best regards

Allan