Form questions...

[gsempcb]

(Keep in mind my questions come from someone with little to no practice of VisualBasic. I understand basically how it works, and have taken one class on it; but am nowhere near fluent in it.)

1. How do you go about having a text field on a form set up so that right when user gets to the form, a Message Box or Input Box (with accompanying space for user to type) appears saying "Enter Date", so that whatever user enters into that message box is entered into the text field? I'd want to set it up this way so that a user (who will be inputting more data on the form associated with that date) gets prompted for it immediately when going to the form. Then after putting it in, it just sticks there and stays around, because there's two more subforms within this main form that are linked to the various data that's input to the form--such as the date--and that repeat the data.

Snapshot of Form​

(just btw, "Week Ending" is the one I want the prompt for, "Developer" is just something that'll be passed through each time, and "Project ID" is a primary key that'll be passed through each time.)

Quote from other topic:

MsgBox is good. But it won't let the user type anything. InputBox will, but not MsgBox.

You can also [ask a Question in square brackets] in a form / report or query. Doing that prompts the user for Enter Parameter Value and that value is returned to the object that has the question in brackets.

By InputBox, do you mean TextBox? I know they're not the same, but I'm not seeing an InputBox on the toolbox of all the form tools (like with TextBox, Label, ComboBox, etc.) And the only help topic in my Help files having to do with "InputBox Function" links me to a Japanese MSDN site. I'm aware of making the "Insert Parameter Value" prompt show up on a query by placing a [statement enclosed in brackets] in the "criteria" section of a given field. However, I do not know where to enclose [a statement in brackets] that triggers an "Enter Parameter Value" pop-up for a text-box on a form. I've tried inserting [Enter Date] in the control source, in the "Build" areas, as simply the name of the text-box, and I just can't seem to get any sort of prompt for user to enter value to pop up like I used to could in the query I was working with. I tried writing a module in VB for an InputBox. But I'm pretty sure my syntax was wrong or I'm not doing something basic, because in the Expression builder, the name of my Module shows up, but I can't seem to select anything out of it.

2. In this same form I am asking about, like I said, it is a main form with two subforms. This is regarding the first of two subforms: Is it possible, when making a subform and linking various fields to the main form (so that whatever user inputs to one field on one form is automatically passed to the given field in the subform), to do this with more than three fields per subform? The create subform wizard only lets me choose three fields to do this with. And when I try to edit in Design view via the "Link Child Fields" and "Link Master Fields" in the Properties for the subform by putting another semi-colon and enter more field(s), it seems to throw the whole set-up off.

3. In this same form I am asking about, like I said, it is a main form with two subforms. This is regarding the second of two subforms: The second subform displays some of the fields that have been entered in by the above two forms in Datasheet view. So

• the main form at the top's got three fields users put stuff in
• the second one, the subform, has got a few fields users put stuff in
• the third one is datasheet-view, and it displays the different fields (but also lets user change it from down there if necessary)

So say I want to set this up to where the datasheet subform makes a new row of data for the different fields, each time that middle subform is filled out, field-by-field. Ok, no problem--it does that already, now. However, what if when I tab out of the last field on that first subform (middle collection of fields), I loop right around to the top of that subform, and enter in some new pieces of data, then when tabbing out of the last field in the middle again, it adds another record/row to the datasheet down below? See currently, it does place a new record in the datasheet-view-subform below, but ONLY one--I cannot add any more, or have a collection of them. These fields do draw from queries and table, so is there a way I can write some sort of macro to take affect such that after a user fills out the last field in the first subform (middle collection of fields) and it puts the data in the datasheet-form, then reloads the query so that when the user loops back to the top of the second form, whatever he inputs into the fields this time will yield a second record/row in the datasheet view down below?

Snapshot of Form​

(just btw, "Week Ending" is the one I want the prompt for, "Developer" is just something that'll be passed through each time, and "Project ID" is a primary key that'll be passed through each time.)

 

[gsempcb]

Ok, so just focusing on that first question right now:


In my form, I went into design view

• Right-clicked desired text field where I want the piece of text displayed that I want to prompt the user to enter
• Clicked "Properties"
• Navigated to "On Got Focus"
• Click Build - " ... "
• Created the following: http://www.rjbfabrication.com/enterday.png[/URL]

Now what? I think the code is wrong, and I do not undestand how to save Modules such as the one I created, and put them on an event such as "On Got Focus". The VB editor sometimes asks me something about creating a macro, and then it will bring up another code window, which I do not know if I need or not. Then, I'm not sure if I'm supposed to open the module I made, or this macro it asked me to make. And whenever I've tried to use this thing I make, it messes up my form.. So I'm clearly lost, with the VB stuff.

 

[ExcelIsEvil]

Wow.
First I would use the .setfocus on the txtbox for 'week ending', that points the cursor there. Your dropdrowns are based on existing data from your MDB. Easy to click on.
The subforms are linked via the parent/child linked fields. I'll assume this form is based on efforts linked to workerbees doing tasks, so the dropdown for DEVELOPER is driving the suform. Your 'value' in dropdownbox (i.e. SMITH, JOE) could be the link to his efforts in other sub forms. Similar to the Project ID.

When Access is asking EVENT or MACRO it depends on your efforts. I have not coded MACROS in years. Too much easy VB code to copy-paste-reuse from previous late night efforts, or web surfing.

The [] parameter is based on the query in your MDB.

SELECT INV_NO, INV_DT, INV_CUST, INV_ADDR
FROM INVOICE
WHERE INV_SALES_MAN = [parameter for prompt]


Take one sub-form at a time, rather then all at once, you will drive yourself crazy looking for errors.

 

[gsempcb]

Wow.
First I would use the .setfocus on the txtbox for 'week ending', that points the cursor there.

Not entirely sure what you mean here.

...so the dropdown for DEVELOPER is driving the suform. Your 'value' in dropdownbox (i.e. SMITH, JOE) could be the link to his efforts in other sub forms. Similar to the Project ID.

Incidentally, the table from which I'm working (that supplies all these fields, and that I'm inputting back into) was originally constructed by someone else. The table has three fields that are primary keys, and two of them are ones I put at the top of the master form that bleed over into the two subforms. Is this relevant?

When Access is asking EVENT or MACRO it depends on your efforts. I have not coded MACROS in years. Too much easy VB code to copy-paste-reuse from previous late night efforts, or web surfing.

tried both

• Just the
  InputBox (EnterDate)
  line of code (http://www.rjbfabrication.com/enterday.png)
• As well as that really long example they give at the bottom of this page--except I had changed a few of the variable names and messages to reflect my particular case.

And I guess due to own limited experience with VB, I could not get it to save correctly, or something (its bring up something about creating a new macro), and I could never link to it correctly from within the expression editor, for that Enter Date text field at the top fo my form. Plus, I am not totally sure if whatever I tried to do was without error--the code itself--either.

Take one sub-form at a time, rather then all at once, you will drive yourself crazy looking for errors.

I just want the data to spew out right across each form, I want the form to remember certain pieces of data when going "back and forth" through records, and I want to be able to add cumulative records to show up in plain sight on the datasheet at the bottom.

 

[gsempcb]

Double-posting I know, sorry, but I do have an update.

Ok, taking a break from the question about having some sort of InputBox/MsgBox/Pop-up box to enter in the date (I'd still like any help and take any suggestions on this, as I'm still not where I want to be with it), here's my next question, regarding forms/subforms.

2. I'm not doing the two-subforms-inside-one-main-form anymore. Here's a snapshot of how I've re-structured this thing:

Snapshot of form-new

So right now, three of them are linked, between form and subform--how can I link all five of them? That is, you put it in at the top, and it goes to the one under it in the datasheet subform. Right now it does it with three of the fields, because that's all the wizard let me set up--is there any way (and if so, how?)--to do it with all five fields?

 

[gsempcb]

Triple-posting, I know--but one of the problems is solved, and I'll post how:

1. I ended up sticking =InputBox("Please enter the date you want:", "Enter a Date") in the control source of the date text box. It's rough'n'ready, and I might be able to sorta play around with some validation stuff and instructing the user stuff. But this is just great for what I'm trying to do.

2. So I'm still wondering/working on getting new form to link all five fields together, somehow.

Overall I want it set up to where the input-user gets asked the date, they put in in, it's reflected both places on the form*. Then, as they input field-by-field, it puts it in the datasheet form, and once they finish one record, they tab out of that back to that second field at top of form (skip date since it's saved), and input field-by-field again and have it reflected in the datasheet at the bottom as a new record under the first one, and so on and so on**. Then once they get enough records put in for one employee, they hit "new record" at the very bottom of the form, it saves the date, but clears everything out--both on the top form, and datsheet subform. Because when they've chosen "next record", they'll be chosing a different employee to fill out data for; and that's the whole reason behind the datasheet view, just to show a summary of different things/different records worked on by a single employee at a time***.

*Right now, this is done! I just need the other two fields linked.
**Right now, after user fills out last field at top of form, it tabs down to datasheet, and I don't want that--I want it to tab to the second field in the top portion. And when I wrote a macro and configured it to "GoToControl" on the event "On Exit", it DOES go to the right record I want next, but then it doesn't put it into the datasheet.
***Right now, when user clicks "New record" at the very bottom, it asks for date again. I don't want that. I want date saved. But anyway, then as I'm filling fields out, if I try to tab out of that last one at the top of the form, I get an error: "Index or primary key cannot contain a Null value", which doesn't make sense because all of the fields at the top are filled out. No idea why this happens.