Parameter Query Blank input creates error (1 Viewer)

GLese · Feb 22, 2018

I currently have a form which is populated by data pulled from a single parameter query [Enter Batch Number to be modified].

When I open the form an the popup appears, if I enter a batch number that exists in the table, it returns the appropriate record to the form.

If I enter an incorrect batch number, or enter nothing at all, I get a blank form.

If I want to set this up so that entering an incorrect value, or no value returns a popup error message, Do I need to set up a VB script in the OnLoad section of the form properties? Or somewhere else?

Is there anything specific I need to be aware of when setting up this type of response?

GLese · Feb 22, 2018

Hold on, may have just found the answer to my own question in this thread:

https://www.access-programmers.co.uk/forums/showthread.php?t=213540

I'm exploring this option right now, but if anyone has other ideas, I'm all ears

Minty · Feb 22, 2018

Normally you would get the data for the batch number from the previous form, and perform the validation before attempting to open the other form.

By doing this you could either use a combo box to make sure the batch exists so the situation never arises, or if not entered (blank) open the form to enter new data as required.

Minty · Feb 22, 2018

GLese said:
Hold on, may have just found the answer to my own question in this thread:

https://www.access-programmers.co.uk/forums/showthread.php?t=213540

I'm exploring this option right now, but if anyone has other ideas, I'm all ears

Great minds etc....

GLese · Feb 22, 2018

Minty said:
Great minds etc....

Now comes the experimenting since I want to drive the form with a combo/list box that populates from a query that will only show the batch #s that were entered in the last 2 calendar days... hopefully I won't break my brain/database too badly

Minty · Feb 22, 2018

You can make a combo row source based on any criteria you need to.

Use the query designer to make a suitable result set then save the sql into the combo row source, or if it could be used in lots of places, save the query and use that.

Mark_ · Feb 22, 2018

For the last two days, I'd check the date entered against (Date-2) and use the built in date function.

GLese · Feb 22, 2018

Mark_ said:
For the last two days, I'd check the date entered against (Date-2) and use the built in date function.

I've got that part down, I'm applying "Between (Now()-2) and Now() to the Date field and that part works.

I've got two options going forward with my design I think.

1) Make a multiple records form that has all the editing fields, with a combo box on the batch number field. When that box is opened by the user it will display every batch number for the past 2 days. When they select it, the whole form will populate with the relevant information from that batch number. (If there are multiple records for that batch number the form would show all in the multiple records format).

2) The other option would be to have a series of forms/queries. One query to pull up the batch numbers in the past two days, a form with a combo box to display and allow users to select the batch number, which will then lead to a multiple records form that displays all records of that batch number which can then be edited. (I say all records here as opposed to the record as in the previous example because for another data table I will be working with later one batch number may have multiple record entries. This form will be useful for both data sets I'm dealing with)

I'm not sure if option 1 is possible, but if it is, I'd rather have fewer forms/queries to keep things simple.

Mark_ · Feb 22, 2018

From the description, you really do need a subform in continuous format for editing. Option 1) would require you to have an entry field for any possible record. Since there are more than 1 possible records, this means (in theory) and infinite number of entry fields.

GLese · Feb 22, 2018

Mark_ said:
From the description, you really do need a subform in continuous format for editing. Option 1) would require you to have an entry field for any possible record. Since there are more than 1 possible records, this means (in theory) and infinite number of entry fields.

Thanks for the tip Mark_, I know nothing on subforms, so I'll start researching that. Just so I have an idea of what you are saying, for option 2, I have a form which will allow me to select the batch, and the subform is what will have the multiple records format to allow for editing on a multi-record basis?

Or do I have that backwards?

Mark_ · Feb 22, 2018

Form 1 would be on the parent and allow you to identify batch. If there is no real "Parent" record, you can still use the combobox to identify batch.

You create a form that is as wide as your parent form for your "Subform". This would then look a lot like the line items on a report. For each record the subform (as a continuous form) creates a single instance to edit that record. Do search on here for issues with continuous forms as they some times act like a single instance but other as if there were only ONE instance (primarily when using unbound controls OR trying to selectively enable/disable controls). Also do a search on here about how you link a subform to a parent form.

GLese · Feb 22, 2018

Mark_ said:
Form 1 would be on the parent and allow you to identify batch. If there is no real "Parent" record, you can still use the combobox to identify batch.

You create a form that is as wide as your parent form for your "Subform". This would then look a lot like the line items on a report. For each record the subform (as a continuous form) creates a single instance to edit that record. Do search on here for issues with continuous forms as they some times act like a single instance but other as if there were only ONE instance (primarily when using unbound controls OR trying to selectively enable/disable controls). Also do a search on here about how you link a subform to a parent form.

After my last post and before you responded I also came across this option.
Is this describing a similar approach to what you are saying?

See the last response on this page: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_winother-mso_2010/need-to-use-listbox-to-populate-a-subform/3da074bb-12f8-44b8-aee3-29f417ddf37b

It seems to make a single continuous form, and have a bound combo box in the header which dictates what information is populated into the form. In looking at how the query builder sets things up, I might be able to do this without having to create a standalone query and pull all the data directly to the form from the initial table of records.

I'm off work now, so I'll check in tomorrow and look around for your suggested issues with continuous forms, as well as experiment with the option I came across (unless there is a big red flag reason not to that I'm unaware of in my new user status.)

Thanks!

Minty · Feb 23, 2018

Just a side note - I would use Date()-2 and Date() rather than Now().

Now includes the time as at when it is called. Date also includes a time portion but set to 12:00am (Midnight). You might get unexpected results using Now() especially in comparisons. Ditto only store Date() 's when you don't need the time portion.

Dates are actually stored as decimal numbers where the integer part represents the day and the fractional part the time. Have a read here for some common "gotcha's"

https://www.techrepublic.com/article/10-tips-for-working-with-dates-in-microsoft-access/

GLese · Feb 23, 2018

Minty said:
Just a side note - I would use Date()-2 and Date() rather than Now().

Now includes the time as at when it is called. Date also includes a time portion but set to 12:00am (Midnight). You might get unexpected results using Now() especially in comparisons. Ditto only store Date() 's when you don't need the time portion.

Dates are actually stored as decimal numbers where the integer part represents the day and the fractional part the time. Have a read here for some common "gotcha's"
https://www.techrepublic.com/article/10-tips-for-working-with-dates-in-microsoft-access/

Good to know. I have the coding for the timestamps set as:

Code:

Private Sub btnRPRLLogout_Click()
  With CodeContextObject
    .DateOut = Date
    .TimeOut = Time()
    End With
    'Timestamps DateIn mm/dd/yyyy, and TimeIn column hhmm (24hr)
    MsgBox "Paperwork Sucessfully Logged Out", vbOKOnly, "Thank You!"
    Beep
    CloseWind
End Sub

So it does store things separately.

I'm confused by the Date storing as the date with the time of midnight. If I say I want to look at the past 2 days/48hrs of records, and I use Now(), it should go back 48 hours from the time the query is pulled. But if I use the Date-2 setup, would it pull from the midnight before the current time? or the one at the end of the current day? I don't know whether VB defined 0000hrs as the end of a day, or the beginning of the day?

Mark_ · Feb 23, 2018

GLese,

Date/Time variables store the DATE portion as an integer (mathematical definition, not variable type) and the TIME as a decimal (mathematical definition once again).

As such, DATE would return a value of say 42153 while NOW would return 42153.4335997 as an example. If you want the past two days, you are looking for DATE-2. For the above example this would give you 42151, thus allowing you to look at all entries for today, yesterday, and the day before.

If you really want a 48 hour rolling time period (so that if you do this at noon today, you are only looking back to noon two days ago, not the beginning of the day two days ago), you would use NOW.

Your post #5 specified

last 2 calendar days

so Minty was pointing out NOW would not meet this requirement while DATE would.

Does this clarify?

Minty · Feb 23, 2018

If you are storing datetimes with an actual time element then yes use Now() to get your accurate 48 hour comparisons. Most of the time with dates you really only want the date bit, that's when the midnight setting starts to make sense.

So today ( Date() ) is 43154.0000000
Now is 43154.6459375

In the immediate window of the VBA editor type ?CDate(43154.0001) See how that works?

Secondly your use of

Code:

 With CodeContextObject

in your code is I think (and please take this the right way) plain wrong even if it's working. I didn't immediately even know what it meant. According to M$ https://msdn.microsoft.com/en-us/vb...application-codecontextobject-property-access
It to identify a error object. I can't remember ever seeing it used before.

The normal way to set an objects value on a form in code is

Me.YourDateControlName = Date()

Me. refers to the current form object. This will enable intellisense in the VBA editor to fill out the available objects for you.
If you type another . after the control name you will see other available properties such as

Me.YourDateControlName.Enabled

Etc.

GLese · Feb 23, 2018

Mark_ said:
GLese,

Date/Time variables store the DATE portion as an integer (mathematical definition, not variable type) and the TIME as a decimal (mathematical definition once again).

As such, DATE would return a value of say 42153 while NOW would return 42153.4335997 as an example. If you want the past two days, you are looking for DATE-2. For the above example this would give you 42151, thus allowing you to look at all entries for today, yesterday, and the day before.

If you really want a 48 hour rolling time period (so that if you do this at noon today, you are only looking back to noon two days ago, not the beginning of the day two days ago), you would use NOW.

Your post #5 specified so Minty was pointing out NOW would not meet this requirement while DATE would.

Does this clarify?

Yes!!! Thanks so much Mark, all of this stuff is starting to make sense

GLese · Feb 23, 2018

Minty said:
If you are storing datetimes with an actual time element then yes use Now() to get your accurate 48 hour comparisons. Most of the time with dates you really only want the date bit, that's when the midnight setting starts to make sense.

So today ( Date() ) is 43154.0000000
Now is 43154.6459375

In the immediate window of the VBA editor type ?CDate(43154.0001) See how that works?

Secondly your use of

Code:

With CodeContextObject

in your code is I think (and please take this the right way) plain wrong even if it's working. I didn't immediately even know what it meant. According to M$ https://msdn.microsoft.com/en-us/vb...application-codecontextobject-property-access
It to identify a error object. I can't remember ever seeing it used before.

The normal way to set an objects value on a form in code is

Me.YourDateControlName = Date()

Me. refers to the current form object. This will enable intellisense in the VBA editor to fill out the available objects for you.
If you type another . after the control name you will see other available properties such as

Me.YourDateControlName.Enabled

Etc.

Okay, that makes some sense. Since we are more concerned with the past two business days, I should use the Date version rather than the now.

Also, thanks for the info on the codecontextobject. I grabbed that from a piece of code I found somewhere else and it worked. But I've rewritten the code using the Me. procedure and When I run the following brick of code, I get an error, "Compile Error: Method or data member not found" which then highlights: Private Sub btnLoginPaperwork_Click()

Code:

Option Compare Database
Option Explicit
Private Sub btnLoginPaperwork_Click()
    Me.DateIn = Date
    Me.TimeIn = Time()
    'Timestamps DateIn mm/dd/yyyy, and TimeIn column hhmm (24hr)
    MsgBox "Paperwork Sucessfully Logged In", vbOKOnly, "Thank You!"
    Beep
    CloseWind
End Sub
Private Sub ExitfrmRPRLLogin_Click()
    CloseWind
End Sub

Am I missing a statement somewhere that tells the Me. what to do?

GLese · Feb 23, 2018

Hey nevermind my last posting about the Me. not doing something, I figured it out!

The form that Me. was referencing didn't have the DateIn/TimeIn fields physically on it, so the lines Me.DateIn=Date() and Me.TimeIn = Time() didn't know what to reference. I've added those fields onto the form but made them invisible to the end user and everything seems to be working now.

Minty · Feb 23, 2018

Hmmm - I'm not sure tbh.

Make sure it's actually referencing your button - press the build event option on the click property of the cmd button?

In the VBA editor what happens when you click the Debug¬Compile option ?

Edit : See you have sussed it out - well done

Parameter Query Blank input creates error (1 Viewer)

Registered User.

Registered User.

AWF VIP

AWF VIP

Registered User.

AWF VIP

Longboard on the internet

Registered User.

Longboard on the internet

Registered User.

Longboard on the internet

Registered User.

AWF VIP

Registered User.

Longboard on the internet

AWF VIP

Registered User.

Registered User.

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread