Access 97 help: data entry creates new field entry

Factual Questions
1

I was able to figure out my last Access issue just by posting it, but this time around, for the life of me, I can’t figure out how to go about doing what I’d like to do. I’ve learned a lot in the past week (literally, I have never done any Access/SQL/whatever work until last week!), but I think my ambitions have gotten much bigger than my knowledge!

I’ll try and keep this short, as much as possible.

I have a form on startup (though the switchboard, actually) that allows new data to be entered into a specific table. The data to be entered, is, for example, Product, Country, DocumentType, DocumentNumber, RevisionNumber and a few other things that aren’t important for this next step.

What I want to do now, is have that new record data generate a filename, since this name is what the original paper document will be scanned and stored onto a server under. For example, a Letter for SuperWidget300 from Denmark, number ABC-123 Revison 2 will be scanned and saved under the name DENLettABC-123R2.pdf.

I want the user to input the data and receive a popup or a report with that filename on it, so that he or she can copy the number down, go scan the paper and save the file.

I also need this database to store this filename, so that a user can later do a search on (for example) Country, and have the products in that country return the filename appended to the predefined web path, which is either clickable onscreen or printable from a report. I have the report generated, but I haven’t been able to do the append part yet.

Can someone help me figure out how to do this? What features should I be Googling?

I had thought about having the entry form update the table with new data and open a second form which generates and displays the filename, and this second form would have an “OK” button which both closes the form and updates the table to store that name - would this be through an update query?

How can this be done? Something needs to go in the Entry form On Click, but how does Form2 get the newest data to make the name? I don’t understand how Access “thinks” well enough to plan this out, let alone code it (not that I know how to code much at all!) This is too much of a long chain reaction for me to sort out, but I know it can be done, and it will be so spiffy when it works!

Thanks so much for any help you can provide!

2

If you want to do this the way you’ve described, you need to learn some Visual Basic for Applications. VBA allows you to pass parameters between objects (eg forms) so you can get the latest name and hand it over to the new form, although I’m not quite sure why you’d want to do that.

It would be much easier to have a message box pop up with your filename and an OK button (check out MsgBox or MessageBox) and then write the record (using an update query) when the OK is clicked on.

How is your filename being generated? Will more than one person be using this at one time? You’d need to answer these before you can devise a strategy for choosing and displaying filenames.

3

On this page an example is shown how on a form:

  • a control (e.g. textbox) gets a new value from a user
  • the “after_update” event fires for that control
  • the developer has written some VBA code in that event’s procedure that will change another control’s value.

Note that your code should almost always interact with the CONTROLS on the form, not the underlying FIELDS of the record. This is a common point of confusion abetted by MS Access since the wizard will create a control with the same name as the field. I usually rename all the controls on a form from “HomePhone” to “txtHomePhone” (if it’s a textbox)

I wouldn’t necessarily use a second form to interact with the user, an inputbox or messagebox is easier to construct. Form to form communication is an advanced topic while inputbox’s are dead simple.

4

Whatever is simplest, I’m willing to go with it. I just don’t want to have to generate the filename after the submission, like asking the user to do another search to get it back. The user might be inputting more than one file, one after the other, I don’t know. It is unlikely that more than one person will use this at a time.

I’ll look into a message box… that’s a start. I’m really learning this on the fly, and every little bit counts!

I don’t know for sure how to generate the filename. I understand how I want to concatenate the entries to get the format I want, and in fact I have it show up on a Search report if someone is looking for a specific document.

Before going home, I was thinking of doing something along these lines:

In the DataEntry form (I’m using better names, but for this post, I’ll simplify everything!) the user selects Product, Country and DocumentType from comboboxes, enters the DocumentNumber and Revision, and clicks Submit. This creates a new record in my Table. I was thinking of using the OnClick on the Submit button to 1)submit the new record, 2) pass the Country, DocumentType, DocumentNumber and Revision to a new form (but keep them hidden) and have a box of some sort in the new Form being populated with something like =left(Country, 3)&[DocumentType]&[DocumentNumber]&“R”&[Revision] (exact form will depend on what I’m told to do, but I’m fine with that.

This name can be seen and written down by the user, and the OnClick on a button that says “Ok” writes an update query to the Table to fill in Filename=box12 or whatever I name it.

I think this sounds good in theory, but putting it in practice is tougher! Does this process sound reasonable to you?

I’m beginning to think I have to pass the autonumberID for the new entry too, so that the update query knows what entry to update, right? I’m not sure how to do an update to the specific record from this point. Even passing the variables to the new form is something I’m not sure how to do. I found Open.Form and Open Args (I think that’s it… I don’t have the file with me this weekend and I’m sick of Google right now!) for the first form, but I still haven’t quite sorted out how the second form can match the data. There are websites that explain this, but I’m struggling with finding examples similar to what I’m doing so I can tweak them for my needs.

Basically I’m constantly looking for examples of “generic” code to put in specific Event procedures (or other locations) in the types of forms/queries I have, but once I have that, I’m usually able to mess around with it to get it to work.

Step 1: In form1, put [code example] in “Event example”
Step 2: In form 2, put [code example] in “Event example”, and “query” into Onclick, and voilá!

Or is it all actually simpler than that? I’m proud of where I am after only 5 days of working with Access, though. All I’ve ever really done with computers/programming is the very, very basics of C and Fortran! The database itself is simple (and I have a simple copy stored!), so now I’m just trying to make it as easy for the user as possible. Unfortunately, that makes it much, much harder for me!

Thanks for your reply, I hope you can keep pointing me in the right direction, though I feel you’ve already given me a couple of ideas! :slight_smile:
Edit: And thanks for that link - it will help!

5

mnemosyne - at the rate you’re going, we’ll be asking you questions in a few months :wink:

I didn’t read your original note carefully enough. I now see that the filename is not based upon the value of one control, but three or four…

The way to handle that is use the before_update event of the form - it fires when the user wants to save the record (usually by attempting to move to the next record) - your code jumps in just before the save actually occurs and does data validation, or in your case, builds the value of the filename. You can actually cancel the save in your code if you want. See this example

So, here’s what I would do:

  • use an autonumber regardless. It is important to have a key, and in this case there doesn’t seem to be a “natural” key (like SSN would be for people)
  • the filename is a field on the form along with all the others. You may make it read only (not enabled) if you want the code to strictly control it’s value.
  • in the before_update event of the form, write code to check the values of Country, DocumentType, DocumentNumber, RevisionNumber and construct a filename. The inputbox presents it to the user, and the user either adjusts it or accepts it. An Ok click in the inputbox updates the filename and lets the record save finish. A Cancel click in the inputbox sets the Cancel argument TRUE and the record is left unsaved, still in edit mode.

An alternative is to write one line of code in each of the after_update events of Country, DocumentType, DocumentNumber, RevisionNumber… this code calls a Sub procedure that you write called Generate_FileName. In this Sub write the logic that constructs a filename and at the end assign the new filename to the txtFileName control. The user will see the filename changing as they fill in the other fields.

Hope this makes sense!

6

YAY!! That’s exactly the kind of help that I want! Thanks so much! I won’t be able to try it out until Monday (I don’t have Access at home), but I’ve already sent this thread to my work email and I’ll give it a shot! That’s the kind of “thought process” stuff I’ve been confused about… how Access understands things, and in what order. I really think this will work!

Just one little clarification… when you say the “Before Update” event of the form, do you mean on the “Submit” button (the command button events list?) That list has way more options than when I click on a blank area of the form itself. Does the Submit button (and all the other command buttons) control the form, or is this to be found somewhere else? I noticed that when I have two buttons, Submit and Close Form, that the event procedures all show up in the same Code Editor. That’s what you mean, right?

I almost wish I could go in to work tomorrow and try this out. Almost. But instead I’ll be dragon boating and then partying around a campfire at a cottage. More fun :wink:

Thanks again!

7

Be careful - the property inspector will show you properties and events for the form, a form section or a control on the form - it depends on what you have selected. A form has sections, e.g. header, detail, footer. These sections each have their own set of (about 5) event procedures. But you want the main Form object… click on the “magic” square (above the vertical ruler) to select the Form itself. This will show you about 50 event procedures for the form itself including all the ones that react to record changes.

So, I do mean the BeforeUpdate of the Form. If you are in the Visual Basic Editor there is Project Explorer on the left and the code window on the right. The code window has two drop-down lists at the top. The left one chooses objects and the right one chooses procedures. (I’m describing the standard layout of the VBE, hopefully you haven’t hidden or re-arranged anything!). So, choose your form code by double-clicking the specific form you want to write code for in the project explorer, then choose “Form” in the object drop-down, then choose “BeforeUpdate” in the procedure drop-down.

Another “gotcha” is to single-click on a class-object in the Project Explorer… the code window is still showing the code from the previously selected object. You have to double-click (note that the description changes in the title-bar of the VBE window).

It’s not really necessary to have a “Save” button on an Access form, but it’s harmless. The wizard will create some code that essentially chooses the menu option “Record/Save”. I would NOT put validation code in that button! The reason is that there are so many ways (unless you’ve done a ton of property setting and coding) to save a record without the button: use the menu, navigate to the next record, use the tab key or ctrl-down or simply close the form.

In fact, a application developer purist would be very nervous about having a form validate and react to data changes because a user could update the table without even using the form. But that’s another topic or two and it’s getting late! Let’s not worry about bullet-proofing your application just now.