MS Access Multiuser - Nikos Yannacopoulos, please help.

Author

Nikos Yannacopoulos, please help.

jarryd

2005-04-19, 11:24 am

Hello Nikos (or anyone),

Please help me with the unbound subform. I am not too sure how a subform is
nested in a form without it being bound to something that links it to the
main form, which in this case is also unbound. The problem is that the
subform stores all the items that are added to a single order and therefore
is created as datasheet. How do I make an unbound datasheet form that I can
add multiple records to? I know you said to use a recordset but I am not
sure how. Please help.

TIA,

Jarryd

Nikos Yannacopoulos

2005-04-20, 7:24 am

Jarryd,

I suppose this realtes to a previous thread of yours, which was started
in this NG, thus your coming back here... although formscoding would
have been more appropriate for the subject. Anyway, here goes:

As far as the header goes, you only need to add one record, so a plain
append query in VBA, reading directly from the form controls, would be
just fine.

Now, for the line items in the subform, you need some means of storing
them somewhere temporarily, until you are ready to save the header and
thus acquire the order number which is necessary for storing the line
items. An easy way to do it is to use a temporary table with all the
fields in your line items table except for the order number; the subform
must now be bound to this table. Once the code for saving runs and the
order number is calculated, you can then run a second append query in
VBA to store the item records iin the main order items table, and
finally a delete query on the temp table to clear it. Apparently, no
recordset operation required.

To implement the above in code, your code would look something like:

Private Sub cmdSave_Click()
Dim vCust, vDate, vOrderNo, vProductID, vQuantity

'Append header section
vCust = Me.CustomerID
vDate = Me.ReqDeliveryDate
vOrderNo = DMax("OrderNo", "Orders") + 1
strSQL = "INSERT INTO Orders (OrderNo, CustomerID, DelDate)" _
& " SELECT " & vOrderNo & ", " & vCust & ", " & vDate
CurrentDb.Execute

'Append items section
strSQL = "INSERT INTO OrderDetails (OrderNo, ProductID, Quantity)" _
& " SELECT " & vOrderNo & ", " ProductID, Quantity" _
& " FROM TempOrderDetails"
CurrentDb.Execute

'Clear TempOrderDetails section
strSQL = "DELETE * FROM TempOrderDetails"
CurrentDb.Execute

'Clear Form
Me.OrderDetailsSubform.Requery
Me.CustomerID = Null
Me.ReqDeliveryDate = Null

End Sub

Of course, this sample code is not intended to run as is, it is just an
example that will need adaptation to your actual objects (subform name,
number and names of objects on both form and subform, number and names
of tables and fields in them). I hope my choice of example names is
self-explanatory, so you get the idea.

HTH,
Nikos

jarryd wrote:
> Hello Nikos (or anyone),
>
> Please help me with the unbound subform. I am not too sure how a subform is
> nested in a form without it being bound to something that links it to the
> main form, which in this case is also unbound. The problem is that the
> subform stores all the items that are added to a single order and therefore
> is created as datasheet. How do I make an unbound datasheet form that I can
> add multiple records to? I know you said to use a recordset but I am not
> sure how. Please help.
>
> TIA,
>
> Jarryd
>
>

jarryd

2005-04-20, 9:25 am

hi Nikos,

Shot for the advice. Quite simple really, wasn't it. Don't know why I
couldn't think of it. Just the thought of redoing everything got me
flustered at first and then the subform thing. to be honest I have never
tried a SQL statement in VB before. I normally save it as an Access query
and run it, but that query wouldn't read the controls on the current form.
I'll give your code a go (modified) and hopefully I get it right. It is
still going to be some work to get this done, my VBA is throwing up all
kinds of errors at every event for just about every control. But maybe it
won't be as baffling as I thought. Nevertheless, please check this thread
in a few days cos I might not have got it right and need some more help.

So by doing it like this then there should be hardly any chance of
contention for the main tables among the users. I guess it is worth doing.
I have split the DB now and they are using the order for as I originally
created it. At the moment they seem to be able to place orders at the same
time without any hassle, but it is early days yet, so I will try to get this
fixed soon. What about the temp table. What if one is adding records to it
while recoringing an order and the other clicks the save button. That will
clear all the work the other had done. What I will do is create a table in
the front-end Access file. That way each user has their own temp table for
orders.

Another thing that I haven't yet worked out is how to get the linking fields
for the order table and details table updated correctly on the click event
of the save button. To be honest I haven't thought about it properly ye,
but if you have a good simple solution please let me know.

You are right about the posting. My original post was "shared DB for the
first time." It had been some days before I had a chance to try your
suggestions and was worried that you would not go back to that thread. I
also thought you might not read the formscoding NG and remember who I was if
I just put it in there. I apologise for any confusion.

Thank you so much for your help.

Jarryd

"Nikos Yannacopoulos" < nyannacoREMOVETHISBI
T@in.gr> wrote in message
news:uEAflFZRFHA.2788@TK2MSFTNGP09.phx.gbl...[color=darkred]
> Jarryd,
>
> I suppose this realtes to a previous thread of yours, which was started in
> this NG, thus your coming back here... although formscoding would have
> been more appropriate for the subject. Anyway, here goes:
>
> As far as the header goes, you only need to add one record, so a plain
> append query in VBA, reading directly from the form controls, would be
> just fine.
>
> Now, for the line items in the subform, you need some means of storing
> them somewhere temporarily, until you are ready to save the header and
> thus acquire the order number which is necessary for storing the line
> items. An easy way to do it is to use a temporary table with all the
> fields in your line items table except for the order number; the subform
> must now be bound to this table. Once the code for saving runs and the
> order number is calculated, you can then run a second append query in VBA
> to store the item records iin the main order items table, and finally a
> delete query on the temp table to clear it. Apparently, no recordset
> operation required.
>
> To implement the above in code, your code would look something like:
>
> Private Sub cmdSave_Click()
> Dim vCust, vDate, vOrderNo, vProductID, vQuantity
>
> 'Append header section
> vCust = Me.CustomerID
> vDate = Me.ReqDeliveryDate
> vOrderNo = DMax("OrderNo", "Orders") + 1
> strSQL = "INSERT INTO Orders (OrderNo, CustomerID, DelDate)" _
> & " SELECT " & vOrderNo & ", " & vCust & ", " & vDate
> CurrentDb.Execute
>
> 'Append items section
> strSQL = "INSERT INTO OrderDetails (OrderNo, ProductID, Quantity)" _
> & " SELECT " & vOrderNo & ", " ProductID, Quantity" _
> & " FROM TempOrderDetails"
> CurrentDb.Execute
>
> 'Clear TempOrderDetails section
> strSQL = "DELETE * FROM TempOrderDetails"
> CurrentDb.Execute
>
> 'Clear Form
> Me.OrderDetailsSubform.Requery
> Me.CustomerID = Null
> Me.ReqDeliveryDate = Null
>
> End Sub
>
> Of course, this sample code is not intended to run as is, it is just an
> example that will need adaptation to your actual objects (subform name,
> number and names of objects on both form and subform, number and names of
> tables and fields in them). I hope my choice of example names is
> self-explanatory, so you get the idea.
>
> HTH,
> Nikos
>
> jarryd wrote:

Nikos Yannacopoulos

2005-04-20, 9:25 am

Jarryd,

See answers below.

Nikos

jarryd wrote:
> hi Nikos,
>
> Shot for the advice. Quite simple really, wasn't it. Don't know why I
> couldn't think of it. Just the thought of redoing everything got me
> flustered at first and then the subform thing. to be honest I have never
> tried a SQL statement in VB before. I normally save it as an Access query
> and run it, but that query wouldn't read the controls on the current form.
> I'll give your code a go (modified) and hopefully I get it right. It is
> still going to be some work to get this done, my VBA is throwing up all
> kinds of errors at every event for just about every control. But maybe it
> won't be as baffling as I thought. Nevertheless, please check this thread
> in a few days cos I might not have got it right and need some more help.
I will. Don't worry about the errors, it's part of the learning process!

>
> So by doing it like this then there should be hardly any chance of
> contention for the main tables among the users.
Correct.

I guess it is worth doing.
> I have split the DB now and they are using the order for as I originally
> created it. At the moment they seem to be able to place orders at the same
> time without any hassle, but it is early days yet, so I will try to get this
> fixed soon.
Just a matter of probabilities. Given enough time it will happen.

What about the temp table. What if one is adding records to it
> while recoringing an order and the other clicks the save button. That will
> clear all the work the other had done. What I will do is create a table in
> the front-end Access file. That way each user has their own temp table for
> orders.
>
> Another thing that I haven't yet worked out is how to get the linking fields
> for the order table and details table updated correctly on the click event
> of the save button. To be honest I haven't thought about it properly ye,
> but if you have a good simple solution please let me know.
True, if you put the temp table in the back end, but this is not what I
had in mind - sorry, I should have clarified. The temp table lives in
the FE, so each user's temp table is completely independent from any
other's. In theory you could use a table in the BE by adding an extra
field for username so as to avoid conflicts, but (a) local is better
form performance, and (b) adding and deleting records always causes a
certain amount of bloat, so it's better that this happens in the FE
which you can Compact on Close.

>
> You are right about the posting. My original post was "shared DB for the
> first time." It had been some days before I had a chance to try your
> suggestions and was worried that you would not go back to that thread. I
> also thought you might not read the formscoding NG and remember who I was if
> I just put it in there. I apologise for any confusion.
No problem, my point really was that the right NG potentially improves
your chances of getting a good answer (although in practice most of the
MVPs and other responders go through most groups, it seems...). I might
have missed it (not likely) but someone else would have picked it up!

>
> Thank you so much for your help.
Welcome.

>
> Jarryd
>
> "Nikos Yannacopoulos" < nyannacoREMOVETHISBI
T@in.gr> wrote in message
> news:uEAflFZRFHA.2788@TK2MSFTNGP09.phx.gbl...
>
>
>
>

jarryd

2005-04-22, 11:24 am

Hi Nikos,

So far I have had some minor, yet unexpected, hassles but I have managed to
get through most of what is on the main form. One thing I can't get to work
is to SetFocus to a control as part of an event. Access tells me I have to
save the field before I use the SetFocus method, Run-time error 2108.

Any ideas how I get around that?

Cheers,

Jarryd

"Nikos Yannacopoulos" < nyannacoREMOVETHISBI
T@in.gr> wrote in message
news:%23skuWraRFHA.2932@TK2MSFTNGP09.phx.gbl...[color=darkred]
> Jarryd,
>
> See answers below.
>
> Nikos
>
> jarryd wrote:
> I will. Don't worry about the errors, it's part of the learning process!
>
>
> Correct.
>
> I guess it is worth doing.
> Just a matter of probabilities. Given enough time it will happen.
>
>
> What about the temp table. What if one is adding records to it
> True, if you put the temp table in the back end, but this is not what I
> had in mind - sorry, I should have clarified. The temp table lives in the
> FE, so each user's temp table is completely independent from any other's.
> In theory you could use a table in the BE by adding an extra field for
> username so as to avoid conflicts, but (a) local is better form
> performance, and (b) adding and deleting records always causes a certain
> amount of bloat, so it's better that this happens in the FE which you can
> Compact on Close.
>
>
> No problem, my point really was that the right NG potentially improves
> your chances of getting a good answer (although in practice most of the
> MVPs and other responders go through most groups, it seems...). I might
> have missed it (not likely) but someone else would have picked it up!
>
> Welcome.
>
>

Sponsored Links