Replies

Thanks Ken for the quick reply. That worked perfectly.

Thanks for the explanation. Try changing

Format([AIP_LatestAIPDate], & Chr(34) & “dd-mm-yyy” & Chr(34)) & (Chr(10))+[tlkpCabAIPDate].[AIPChangeComments]

to

Format([AIP_LatestAIPDate], “”dd-mm-yyy””) & Chr(10)+[tlkpCabAIPDate].[AIPChangeComments]

and similar for the others.

Yes, that’s working now – Thanks Hans, much appreciated.

Why do you want to format dates in a query? Excel recognizes dates from Access!
And why do you include line feeds Chr(10) in the dates?

HI Hans,

I’m using line feeds because I’m trying to join two fields – Eg, the [AIP_Date] field is actually a combination of the [LatestAIPDate] field and the [AIPChangeComments] field. Because of this, I need to format the dates before they go into Excel.

Hope this makes sense.

Cheers,
Jason

Further to Hans comments that tell you how to remove the Tab control.

A tab control allows you to have several ‘pages’ within the one form.

Tab controls only have a point when they have more than 1 tab (or page). The db you posted has a form with a tab control that has just one page (tab). In that case it makes sense to follow Hans advice and just remove the tab control and move the other controls directly to the form itself.
However, if the real db has multiple pages (tabs) that option would become complicated as there would be other issues to deal with.

Hi Hans and John,

Thanks again for your help. My database does have multiple pages (I just removed for them for the demo I posted), so I think I’ll leave this one for now and let the users know they’ll just have to use the spell check button on the top of the form.

Cheers,
Jason

Hans mean the Tab Control on your form:

Hi Francois (and others) – once again, thanks for your help so far, but I’m still not sure what I’m supposed to remove or modify in order to get spell check to work (Also bear in mind that I didn’t design this database from scratch and am also a newbie to Access!)

If I go to the properties of the page (Eg, Proposal page), I don’t see anything mentioned about tabs. If someone can point me in the right direction as to what I need to remove or modify that would be great.

Cheers,
Jason

The problem appears to be caused by the tab control. If the text boxes are on a tab control, the focus doesn’t shift if you cancel the spell check, but without the tab control, focus does shift.

This appears to be a bug in Access, I don’t know a solution (except doing without the tab control).

Hi guys,

Thanks for your help so far. I’m still trying to figure out why mine doesn’t work, but the example that John posted does work.

When you say tab control, do you mean that you can tab to the fields because they are part of the tab order (View>Tab Order)? If so, John’s example has the same setup but his works, so again I’m not sure why mine doesn’t.

Thanks,
Jason

1) The Find dialog does cycle. It stops when all records have been searched once.

2) To make Access search any part of the field, replace the line

DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

with

SendKeys “%ha%n”
RunCommand acCmdFind

3) You could place a combo box next to the command button from which the user can select a field. The code would then set focus to the corresponding control instead of to Screen.PreviousControl.

Thanks Hans, that has resolved my issue.

Cheers,
Jason

I attach a demo that uses your code, and works without problem for me.
Does it work for you?
If it does, can you post a similar example that does display the problem you describe?

Hi John,

Yes, the demo you attached works for me.

I’ve attached a cut-down copy of my database which just includes a single form and table – I’m still having the problem in this database.

Cheers,
Jason

Do you have other event code for the BillTitle text box? I can’t reproduce the problem that you mention (in Access 2002 SP3) – if I ignore a suggestion or cancel the spell check dialog, the focus moves to the control I clicked in.

HI Hans,

There is no other event code for the BillTitle text box. I’ve also tried this code in another text box (that also does not have any other event code) and I get the same problem. I’m using Access 2003. Any other ideas?

Cheers,
Jason

Thanks Hans for helping, that is exactly what I wanted to do.

Cheers,
Jason

You could indeed add a Department column to the Portfolio table.
Then set the Column Count property of the Portfolio combo box on the form to 2.
If you want the user to see the department in the dropdown list, set the Column Widths property to (for example) 1″;1″
If you would prefere the department not to be displayed in the dropdown list, set the Column Widths property to (for example) 1″;0″
A column with width 0 will be hidden.

Create an event procedure for the After Update event of the Portfolio combo box:

Code:

Private Sub Portfolio_AfterUpdate()
  Me.Department = Me.Portfolio.Column(1)
End Sub

Columns in a combo box or list box are counted starting at 0, so Column(1) is the second column, corresponding to the Department field.

Have you made the AutoNumber field the primary key of the table? If not, try that.

wow, such a quick reply and it’s actually solved my problem! Thankyou so much, you’re the best!

If you want to include a double-quote ” within a quoted string, you have to double it to “”, otherwise VBA interprets it as the end of the string.
Consequently, if you want to include the code for an empty string “” within a quoted string, you have to use “”””.

So you have to replace the 10 occurrences of “” in rsSQL with “”””.

Alternatively, you can replace expressions such as

IIf(IsNull(tlkpCabinetCommitteeDate.Comments),””,Chr(13) & Chr(10) & tlkpCabinetCommitteeDate.Comments)

with

(Chr(13) & Chr(10))+tlkpCabinetCommitteeDate.Comments

The + operator when used with strings usually has the same result as & but with one exception: if either of the operands is Null, + results in Null too.

So “Jason” & Null = “Jason”, but “Jason”+Null = Null.

HI Hans, thanks for your help, that fixed the problem!

Cheers,
Jason

In the query grid when you look at an update query in design view there is a row labelled “Update To” and you select the field you want to update in the top row and then specify the value you want that field updated to as Forms!MyFormName!MyFieldName – you will have to substitute your appropriate names. In your case I think it would be Forms!ProgramMovements!AIP_LatestAIPDate – however your field name be something slightly different, so check that.

Hi guys,

Thanks for all your help so far, I just want to make sure I have this right because I think I might have confused everyone with field names.

What I’m trying to achieve is that when a user enters data into the ‘DeptAIP’ field on the ‘BidDataEnter’ form, I want the ‘LatestAIPDate’ field on the ‘ProgramMovements’ form to be updated. The respective fields and tables that these form fields refer to are ‘AIPProposed_DeptsProposedDate’ in the ‘tblBill’ table and ‘AIP_LatestAIPDate’ in the ‘tblBill’ table – so in this respect, I want it so that when the ‘AIPProposed_DeptsProposedDate’ field is updated in the ‘tblBill’ table, for the ‘AIP_LatestAIPDate’ field in the same table to be updated.

So I’ve created an UpdateQuery (called ‘UpdateLatestAIP’), put ‘AIP_LatestAIPDate’ in the top row (ie, the field in the table I want to update), and put ‘Forms!BidDataEnter!DeptAIP’ in the Update To part (ie, the value in this field should be copied into ‘AIP_LatestAIPDate’). I’ve then gone into the ‘DeptAIP’ field on the ‘BidDataEnter’ form, clicked in the ‘AfterUpdate’ section and put in this code:

Private Sub DeptAIP_AfterUpdate()
DoCmd.OpenQuery “UpdateLatestAIP”, , acReadOnly
End Sub

This doesn’t seem to be working however as it says it’s going to update 4 rows and then when I close the form I get a conflict message.

Once again, sorry for not explaining myself properly!

The easiest option is probably to create an Update Query, that uses the BillBidInceptionNo from the open form as a parameter.

Do you know how to create update queries?

The After Update event would then just run the query.

Docmd.OpenQuery “qryYourQuery”

Hi John,

I’ve worked out how to create a new query and then change it to an ‘update query’, but not sure how to modify this query so that it accepts data from the field on the form.

You did indeed ask that question at the start. But if the form had been open, that would have been easier.

You need to be able to identify which record you want the field updated for.
So is there something on the currently open form that identifies which record? Or is there some other way ?

Usually you would need to specify a value for the key field in the table you want updated. What is the Key? What type of data is it?
And what type of data is AIP_LatestAIPDate – presumably a Date field.

The ‘BillBidInceptionNo’ identifies the record. This is an AutoNumber.

AIP_LatestAIPDate is a Date/Time field.

Cheers,
Jason