Solved Form Validation and Form-to-Form data (1 Viewer)

[wmix]

Hello Everyone.
I've run into a couple of issues and from all my reading and watching of tutorials I'm guessing I need to write some code to get to where I want to go. I could really use some help. (note, I'm using Access 2000)

I have a simple unbound data entry form (FormA) that collects this data:
Truck
WeekDay
CollectionDate

Then I have a button called "Enter Collection" and when a user hits this button I need multiple things to happen.


1. I need to validate that the user has entered a value into the three fields, Truck, WeekDay and CollectionDate.

I am really struggling with validating these three boxes. The other forms I've used are all bound to a table so it's easy to make a field required. Since this is an unbound form I'm not even sure how to begin. I've been reading and watching a lot of YouTube but I don't think I'm searching for the correct topic because I cannot seem to pinpoint how to do this.


IF the user has entered the three fields then I need to move on to the next steps (not sure if this is the proper order to do them or not).

2. Open FormB (has a SubForm in it also)
Several things happen on this form:

A) Upon opening the form the user is prompted to enter the Truck and DayWeek - manually entered the form works great. However, I would like to have the information from FormA automatically enter now.

B) This form has a Subform that is tied to a table for data entry. One of the things the user has to enter in every record is the CollectionDate - but it's the same for all the records the user is going to enter. This is why I want to collect this from FormA and then automatically enter it for the user. Is this possible?

I greatly appreciate your help. Thank you.

 

[sneuberg]

Edit: See missinglinq's post about validating the data.

You can pass this data to another form in the OpenArgs as describe by pbaldy here.

But this procedure sounds strange. Why not entered the data directly in form B. and unless the CollectionDate is a foreign key it should not be repeated. It would help if you explained more about what you are trying to do. If you could upload the database that wouldn't hurt either.

 

[missinglinq]

How is TextBoxName_BeforeUpdate going to fire, if no data is entered into TextBoxName?

As to the validation, you can do that like this:

Private Sub Enter_Collection_Click()

 If Nz(Me.Truck, "") = "" Then
   MsgBox "A Truck Must Be Entered!"
   Truck.SetFocus
   Exit Sub
 End If
 
 If Nz(Me.Weekday, "") = "" Then
   MsgBox "A Week Day Must Be Entered!"
   Weekday.SetFocus
   Exit Sub
  End If
  
 If Nz(Me.CollectionDate, "") = "" Then
   MsgBox "A Collection Date Must Be Entered!"
   CollectionDate.SetFocus
   Exit Sub
 End If
 
[COLOR="Red"]'Place code here to Open Form and send it data from your Controls
[/COLOR]
End Sub

You can use the OpenArgs parameter (have a look in Help) to send the values you need to the secondary Form, then use that Form's OnLoad event to go to a New Record and enter the data, using OpenArgs, into your Controls.

Linq ;0)>

 

[wmix]

Thank you missinglinq and sneuberg for the information.

I am uploading the test database I use before implementing things in my working database. It gives a small sample of what I'm working with.

Validation Code
I added the validation code to my form but something is not working. I get a run-time error after the message box. Not sure what I'm missing but it's like I need a Do Until Loop to make sure all three boxes are entered? Thoughts?
**UPDATE - If I remove the SetFocus from the IF statements then the validation works. Any reason I should not remove this? Does the OpenArgs need this to work?

Forms
I need to enter collections into a table for vending machines, this is why I'm doing all this. I need the form to be as easy as possible so any of the employees of the company or a new hire can enter the data without issue.


Currently I can get the data to go into the table if I do all the following - manually - in this order:

1. User clicks on Truck Collections from the main menu - opens frmTruckCollections

2. User enters Truck, Week/Day, Collection Date and clicks the button Enter Collections. This currently opens a query that shows the user the qryRouteFillOrder (I do not want the user to even see this but I need the information for the next form).

3. I leave both the above forms open. Then from the form menu I click on frmMachineCollections. The user is now in a data entry table. Technically, all the data is entered in the subform, frmMachineCollectionsSub, based on the machine name they see in the main form.

When I do all these steps manually everything works and the data is entered into the table properly. Then I close everything.

However, I would really like it if after Truck Collections is selected from the main menu and the user enters the Truck, Day/Week, Collection Date, that the user is immediately brought to the data entry form and starts entering data.

So I'm trying to get a few things to happen.

1. Validate the user has entered the three required fields. After validation this form hidden so the user no longer sees it. Can it be closed? Not sure if I have to pass data to another table?

2. Query pulls the proper data that will be used in frmMachineCollections (this query is hidden, the user does not need to see it)

3. frmMachineCollections opens

4. The Collection Date pre-populates into all of the "Date" fields as the user enters the information on frmMachineCollectionSub

5. When all the data has been entered and the user closes the form the other forms/queries all close.

I'm still pretty new to Access so I'm not sure if this is the correct way to do this or not - thoughts/suggestions would be greatly appreciated. Thank you!

 

[wmix]

Okay, I have made progress but still need a little bit of help.

1. I was able to get the validation to work, thank you missinglinq.

2. I was able to get the OpenArgs with the Split function to pass the data from the first form to the second form. I no longer open the Query - things are working perfectly (OnLoad worked great to get the data I needed).

3. I have one last issue - the date. I'm still trying to figure this part out.
I am able to get frmTruckCollections to pass the CollectionDate to frmMachineCollections.

But now I need that CollectionDate value to pass to another form - where the data entry is happening - frmMachineCollectionsSub

Thoughts on how I would get this done?

Thank you!!

 

[wmix]

Hello Everyone. I've been working on my database (a lot of cleanup and fixing things to get everything normalized and now I'm back to working on things I need to get up and running. So I am creating a data-entry form for end-users to input the money collected from vending machines.

The company has three trucks that go out and collect money.
The person doing the data entry (right now me) needs a form to enter all the data. I normalized my database by creating a main table (tblCollection) and secondary table (tblCollectionDetails) to collect all the information about the money brought in from the trucks.

I then created a simple form for the end-user to make the proper selections about the money they are going to enter into the database (frmTruckCollections). I put a button to this form on the Main Menu. The user will select it then they have to select a Truck, Week and Day and Collection Date. If they do not do any of these things they will get an error - forcing them to enter this information. This part works.

With this information a new form opens frmCollection.

Theoretically, this form is the form I would like the user to enter the information. However I'm running into a few issues. I figure I'll tackle one at a time. So here's the issue I'm working on right now - CollectionDate.

(Note - there are a lot of fields on the form that the end user will eventually not see, I have them visible for testing purposes).

So I know the CollectionDate is correct as I've created the following:

MsgBox "Collection Date is " & CollectionDate

The message box appears and shows the correct date the user has selected, so I have that information. However, I cannot seem to "put" this date anywhere on my form. (eventually this Message Box will go away - but this is how I tested the information was correct).

The form I'm using does not have bound CollectionDate field. So I tried to create a text box where I could see the CollectionDate, doesn't work. No matter what I try I get an error. Here are some of the things I've tried:

Text Box
Name................CollectionDate
Control Source...=[CollectionDate]

Result - the Text Box reads #Error

So then I thought I would not "close" the original form where I collect Truck, FillID, and CollectionDate and instead I would tell the forms the information. This worked - in the sense that I could "see" the date BUT the table never updates with this date.

I've attached my database sample. It's a work in progress - I use this one for testing and I know it has some issues. I appreciate any insight you may have to get this up and working.