Solved

Do I require an update query?

Posted on 2009-04-14
12
238 Views
Last Modified: 2013-11-28
Hi
I have three tables, one for client details, the other two are client reports and client visits.
They are related by ClientID, and thus are one to many (the one side being the client detail side).

I have a a main form, and two sub forms.  The main form selects the client, and the two sub-forms select the latest report data and visit data.  These two subforms are based on normal queries and work fine.

On my two subforms, I have a button whereby a user can 'add a new report' or 'visit' for a new month.  This button in both cases is simply:
Private Sub NewMonth_Click()
On Error GoTo Err_NewMonth_Click
    DoCmd.GoToRecord , , acNewRec
Exit_NewMonth_Click:
    Exit Sub
Err_NewMonth_Click:
    MsgBox Err.Description
    Resume Exit_NewMonth_Click
   End Sub

The problem is this:
When I click on 'new month' I cannot input data into the form.  I can edit existing data that loads up but if i click new month it won't let me do it.  Is this because of my relationships and 'joins'?  Or is this because I don't have it set as an update query?  Any help much appreciated!
0
Comment
Question by:bo8482
  • 6
  • 6
12 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 24139270
" Is this because of my relationships and 'joins'"

It could be.
Post the sql view of the recordsource of your problem subform.
0
 

Author Comment

by:bo8482
ID: 24139344
here is SQL for one of the subforms, but the other is pretty similar....

thanks for any help
SELECT tblClientDetails.ClientNumber, tblLedgerRecStatus.MonthEndPackDate, tblLedgerRecStatus.SalesLedgerRecd, tblLedgerRecStatus.AgeingReserves, tblLedgerRecStatus.CreditorsLedgerReceived, tblLedgerRecStatus.BankStatementsReceived, tblLedgerRecStatus.ReconciliationRecd, tblLedgerRecStatus.ReconciliationProcessed, tblLedgerRecStatus.Contras, tblLedgerRecStatus.Notes

FROM tblClientDetails LEFT JOIN tblLedgerRecStatus ON tblClientDetails.ClientNumber = tblLedgerRecStatus.ClientNumber

WHERE (((tblClientDetails.ClientNumber)=[Forms]![EditAllRecords]![Combo15] And (tblClientDetails.ClientNumber) Is Not Null))

ORDER BY tblLedgerRecStatus.MonthEndPackDate DESC;

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 24139471
If this is a subform is linked to the main form by client id then you don't want the client table involved at all.
All you need is the tblLedgerRecStatus table.

SELECT tblLedgerRecStatus.ClientNumber, tblLedgerRecStatus.MonthEndPackDate, tblLedgerRecStatus.SalesLedgerRecd, tblLedgerRecStatus.AgeingReserves, tblLedgerRecStatus.CreditorsLedgerReceived, tblLedgerRecStatus.BankStatementsReceived, tblLedgerRecStatus.ReconciliationRecd, tblLedgerRecStatus.ReconciliationProcessed, tblLedgerRecStatus.Contras, tblLedgerRecStatus.Notes
FROM  tblLedgerRecStatus
ORDER BY tblLedgerRecStatus.MonthEndPackDate DESC;
0
 

Author Comment

by:bo8482
ID: 24139613
OK I started to get automation errors when I removed that (not entirely sure why but I think because they were linked to the mainform via ClientNumber.)

I'm beginning to get a little muddled here....

The main form where you select the client number, should that be bound to a table?  Or should I leave it unbound with a select query? (I want a combo box that lists all client numbers)

Then the two subforms - currently they are based on a query.  Should I link these to the main form via client number child/parent?  Or should i just use VBA/expression to input the client number that is selected in the main form?  What is the best way of doing that do you think?

Thanks

0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 24139971
"The main form where you select the client number, should that be bound to a table?  Or should I leave it unbound with a select query? (I want a combo box that lists all client numbers)"

OK I made the incorrect assumption that the main form was bound to the client table because of the statement "The main form selects the client".

If the main form is unbound Access doesn't offer you the fields for the subform link, but you can still set them.

Set the link child to ClientID and set the link master to
Forms!mainformname!comboboxname

Then in the afterupdate event procedure for the combo box you put:
Me.subformname.Form.Requery

There are other ways of achieving the same thing.

But the key issue is getting rid of the Client table from the subform.
That is making the records non-updateable.
0
 

Author Comment

by:bo8482
ID: 24146456
Sorry about the late reply, I had internet connection issues this morning...

That now works I'm pleased to say - however the form does not load up blank now - it loads up the first record in the query.  How do I get rid of that, so that users don't accidentally keep changing the first record?
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 77

Expert Comment

by:peter57r
ID: 24155149
Are you setting a default value for your combo box?
0
 

Author Comment

by:bo8482
ID: 24156507
No I'm not.  I've got criteria in the ClientNumber of myform.[clientnumber] and is not null

I then set default value to null in the form on load event.

I tried to remove the recordset and then update the recordset after update off the combo....but that returned #name? errors on load.

I think i'll have to just set the default value of my combo to the first record so that at least it does not look like the records in the subform are orphaned.  Unless you have any ideas that can help?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24157748
I don't see why you would get a record diplayed in the subform if there is no selection in the combo...
(unless you have a subform record that has no clientid set up?)

I am running a test form here as we go through this and on mine, the subform shows a new empty record when I open the main form.
0
 

Author Comment

by:bo8482
ID: 24158100
I wonder if its anything to do with the wizard generated code - I used the wizard for my combo box...

Code is below.

I may try and rebuild the form from scratch to see if that works.

I will award the points though on the basis that you answered my original question of fixing the non-update issue....muchas gratias!
Private Sub Combo15_AfterUpdate()

    ' Find the record that matches the control.

    Me.Requery

    Dim rs As Object

    Me.RecordSource = "tblClientDetails"

    Set rs = Me.Recordset.Clone

    rs.FindFirst "[ClientNumber] = " & Str(Nz(Me![Combo15], 0))

    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    

    End Sub

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 24158310
We appear to have switched back to a bound main form again (the code you have just posted would not be possible to create if the main form is unbound).  You said earlier that you were using an unbound main for,
Some of the stuff I've posted is completely unnecessary if the main form is a bound form.


0
 

Author Comment

by:bo8482
ID: 24158391
well that code stays in there whether it is bound or not....

I will try removing the code and unbinding the main form to see if that works
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Optimizing a query 3 34
Dropdown Not In List - not working correctly 11 38
grouping logic 6 49
Common Records between Sub Queries 4 15
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now