Replies

The back end is in SQL Server. I will look into triggers and see if they will suit my needs. Thanks for the tip, Wendell!

This is something I’ve been wanting to implement in my time tracking application. I get calls from users on occasion that the application mysteriously changed something and I expect it is a training issue. It would be great to be able to go into a table and see exactly what was changed by whom etc. Can you share any more info on the subject? Or should I make this a separate post?

I created an application that tracks employee vacation. I use a status field to keep track of whether they are active terminated or deleted. When the employee leaves the firm their status changes to terminated. After two years their status changes to deleted and they no longer appear on the form. This is in case they are rehired and they want there old accrual levels. The back end is on SQL server so storage is not an issue and there are not huge amounts of records.

I have something similar in one of my apps and what I did was to set the control source of the textbox to the column of the combobox.

=[cboRCODE].[Column](1)

where [cboRCODE] is the name of the combobox control and [column](1) refers to the second column of the combobox.

Depends on how your db is setup. Are the tables in the same file as the forms and reports? If so then changes made to one will not be reflected in the other db. If this is the case I would recommend splitting the 97 version into a front end and a back end where only the tables are in the back end and all other objects are in the front end. Then convert the front end to access 2k and link to the access97 back end. Microsoft doesn’t recommend this (probably because they want you to upgrade everyone to A2k) but I’ve done this with AccessXP without any problem. Ideally, the best solution would be to upsize the back end to SQL server but that is not something taken lightly.

Take a look at this thread – Click Here – it seems to deal with a similar issue.

If you can’t control the data types during import then you should be able to use one of the built in data conversion functions in a query to convert the text fields to number fields that you can then perform calculations on. Take a look at the online help articles concerning converting data types expecially the keyword summary. Post back if you need specific help to use these functions in a query and I’m sure someone can give you a nudge in the right direction.

Assuming you have a plant table with the following fields:

PlantID
NameCom
NameSci

In the row source either enter the following SQL statement or create a query based upon the following SQL statement and select the query as the row source.

Select tblPlant.PlantID,tblPlant.NameCom AS [Common Name], tblPlant.NameSci AS [Scientific Name]
From tblPlant
Order By tblPlant.NameCom

Make the bound column column 1 (PlantID) and make the column widths 0″;1″;1″. This will store the PlantID in the table and not the plant name. You can retrieve the plant name any time you need to in a query by linking the two tables on PlantID.

Well, it’s true I haven’t met that many Access developers. And come to think of it – with the exception of one or two – the ones I have met haven’t been that sharp. I’m the only developer here and it does get kind of lonely at times. But on the bright side I don’t have to suffer any foolish crud either.

It is my understanding that once you leave the form and focus shifts to the subform the record on the main or parent form is no longer in edit mode so therefore should not be locked. I went with the subform for consistency since the other tabs contain subforns as well in my app. A modal pop up form should work as well but I would test thoroughly whichever way you decide to go.

I seem to remember there is a builtin function that returns the numeric portion of a string but I can’t seem to find it. Anyone?

I would have to strongly agree with the opinion that access apps qualify as “customized software” with one exception. That is if you were to use one of the built in wizard apps and try to pass it off as “custom” but I don’t think any developer worth their weight in bits would do that.

Very interesting thread. Here are my 2 cents. I recently have been having some issues with a memo field when I upsized an app to SQL 7. My resolution includes adding a tab to the tab form, putting a subform on the new tab which is bound to a new comments table where I record the date each comment is entered and each comment gets its own record. The comments table has a one-to-one relationship to the parent table – in my case employees. Though you may not want a separate record for each comment it might help your situation since once the user enters the subform they are no longer on the main form record.

Try this one. It was created in a vanilla nonsecure environment.

Yeah, it’s a A97 version. I’ll try to post a 2k version fo you tomorrow.