in form- drop down and controlled date

[Ruth]

Hi there

I am creating a form and I have 2 questions....

1. I have created a combo drop down box using the wizard. It lists the
customer with the customer code beside it all in one box- it pulls the data
from 2 different columns in a table. When one enters the data it only will
allow it to record the customer, but I want it to record the customer and
customer code in different columns... in a different table along with other
data entered for the request.

How do I get it to record both of them?

2. In the same form I have places for the dates to be entered, date the
request was made, date the 1st attempt to fulfill it, date when 2nd attempt
was made, date when 3rd attempt was made, date when 4th attempt was made and
date request completed. I want to make sure the dates entered are equal to
or greater then the previous date entered. But the 2nd, 3rd & 4th attempt
most likely won't be used each time.

How do I do this?

 

[Ron2006]

Item 1

In the Afterupdate event of the dropdown box code the following:

me.customercodecolumnnameinyourform = me.dropdownboxname.column(1)


All the fields of data returned in a combo box are accessible via
that .column() mechanism.
A dropdown box can return as many columns of info that you want.
The bound column # tells it which one to save
The number of columns field says how many columns are being returned.
The column widths tells access how wide each column should be and can
be 0 (so it doesn't show but is accessable) - separated by semicolon.
The tricky part is that when addressing those columns - The
first column is column(0) - the next is column(1) - etc.

Item 2

In the afterupdate event of each successive date field place something
like

For instance on Date2 place
a) if not isnull(me.date3) or not isnull(me.date4) then
msgbox "This date cannot be erased because there are other
dates"
docmd.cancelevent
else not isnull(me.date2) then
if me.date1 < me.date2 then
msgbox "2nd attempt date CANNOT be less than 1st
attempt."
docmd.cancelevent
endif
endif



Ron

 

[Ruth]

Hi Ron

I am lost...

For the first one I put:

me.customer=me.customer.column(1):me.customercode=me.customercode.column(2)


it states that "The macro (or its macro group) doesn't exist, or the macro
is new but hasn't been saved.
Note that when you enter the macrogroupname macroname syntax in an argument,
you must specify the name the macro's group was last saved under."


The second one I put:

If Not IsNull(Me.2nd attempt) Or Not IsNull(Me.3rd attempt) Or Not
IsNull(Me.4th attempt)Or Not IsNull(Me.completed)Then
MsgBox "This date cannot be erased because there are other"
dates ""
DoCmd.CancelEvent
else not isnull(me.1st Attempt) then
If Me.request date < Me.1st attempt Then
MsgBox "1st attempt date CANNOT be less than request date"
attempt."
DoCmd.CancelEvent
End If
End If



And there is a syntax error in the first line.

 

[Ron2006]

Hi Ron

I am lost...

For the first one I put:

me.customer=me.customer.column(1):me.customercode=me.customercode.column(2)

it states that "The macro (or its macro group) doesn't exist, or the macro
is new but hasn't been saved.
Note that when you enter the macrogroupname macroname syntax in an argument,
you must specify the name the macro's group was last saved under."

The above lines should be in code not as a macro line.
In the AFTERUPDATE Event Press the little button to the right (...
button) and select that you want to create code. then put
me.customer=me.customer.column(1)
me.customercode=me.customercode.column(2)
(the above line seems wrong to me. you are moving it to itself).

The second one I put:

If Not IsNull(Me.2nd attempt) Or Not IsNull(Me.3rd attempt) Or Not
IsNull(Me.4th attempt)Or Not IsNull(Me.completed)Then
MsgBox "This date cannot be erased because there are other"
dates ""
DoCmd.CancelEvent
else not isnull(me.1st Attempt) then
If Me.request date < Me.1st attempt Then
MsgBox "1st attempt date CANNOT be less than request date"
attempt."
DoCmd.CancelEvent
End If
End If

Item 2
Assuming that the first two lines below are actually all one line AND
that the names you are supplying here are example names (because the
space in the middle of the name will not work in code, then what is
incorrect is that
it should read ELSEIF -
If Not IsNull(Me.2nd attempt) Or Not IsNull(Me.3rd attempt) Or Not
IsNull(Me.4th attempt)Or Not IsNull(Me.completed)Then
MsgBox "This date cannot be erased because there are other"
dates ""
DoCmd.CancelEvent
elseIF not isnull(me.1st Attempt) then
If Me.request date < Me.1st attempt Then
MsgBox "1st attempt date CANNOT be less than request
date"
attempt."
DoCmd.CancelEvent
End If
End If

 

[Ron2006]

OOPS. I hit the enter and send by mistake.

The elseif was my mistake in what I told you.

I believe you could put it as elseif not etc

or as

else
if not etc. ' on two lines but you will then need another
endif

Ron

 

[Ron2006]

oops I hit the tab and enter my mistake.
I also made a mistake in the example I sent you.

The ELSE should either be ELSEIF not etc....

or

ELSE
IF not etc........... but if you do it this way you will need
another endif.

Ron

 

[Ruth]

Thank-you for your help! It works now.