Still need help - on form

[Guest]

Haven't heard back still need help see below:

Sound like not a bad idea. It is just that 2 different people will be
inputting the info and the quote person does not need to enter
truck#,ticket#, and some other fields in. HOw can I handle this that the
quote person doesn't see those fields but the invoice person does??????

Thanks,
Barb

So why not just add a field named txtStatus or txtType and use that to
indicate if its a QUOTE or INVOICE?

Was this post helpful to you?

Why should I rate a post?

 

[Guest]

consider lookign at the
User() funtion ie who is logged on

and visible properties
eg
field1.visible= user()="Pat"

this is the wrong sytax, but have a look at it

 

[darkroomdevil]

I just came into this conversation and I don;t see the history of all of the
posts here. I know this is not a direct answer, I am confused as to what
your question is. We do somthing similar so I would like to throw out some
ideas, I hope they will help . I own a photography studio, over the past
15 years I have slowly put together the Access program that we use for
everything now. We often give quotes to customers, this is how we handle it -
without getting into the detail, it is the concepts that are important;

-Our table structure is separate for invoices and quotes - think of the
quotes table as a duplicate of the invoice table without the unnecessary
fileds. For us we have tblInvoice and tblInvoiceDetail for the sales item
activity - tblQuote and tblQuoteDetail for the quote's sales item activity.

-This allows tracking of potential orders, creating an invoice from a quote -
then later on using that same quote to create another invoice (acts as a
template). You can also create a quote from an invoice, if you want to store
it for future use or if the customer backs out of the deal.

-For us a job is a portrait session, we can attach the same job to quotes
and/or invoices (no real point to mentioning this except for the idea that
some common 'information connections' can be used in more than one place).

-You could have a quote 'status' field in case there is any sales/service
follow up needed. Or a contact log table that linked to the quote table if
you needed an itemized history of follow up and triggers to follow up ...
FollowUpDate field kind of thing ...

-Since no money transactions can be attached to a quote, this separates the
database structure and makes it less confusing from a design and usability
point of view.

Hope this helps,
Roger

 

[Guest]

Yes this is pretty much what I am looking for. Not sure how to do it. Right
now the Invoice form is set up and being used. The quotes which is a prior
step is presently being done by hand. Some - (not all) of the fields for the
new quote form would be the same as the invoice form. I would like two
seperate forms like you have. Would also like the invoice from to have a
drop down for MAYBE quote id??? and then it would automatically fill in the
appropriate fields on the invoice form and user would have to only add a
handful of additional info. Having a hard time setting the table up see
below for previous info:

I have created a form to enter records for invoicing for shipment of trucks.

 

[darkroomdevil via AccessMonster.com]

Code behind form
after update event of the combo box or whatever other way that the user
selects whether it is a quote or not

Select Case Me.ctrInvoiceStatus
Case "Invoice"
Me.ctrTruckNumber.Visible = -1
Me.ctrTicketNumber.Visible = -1
Case "Quote"
Me.ctrTruckNumber.Visible = 0
Me.ctrTicketNumber.Visible = -1
End Select

This is the most straight forward - least fancy way

Haven't heard back still need help see below:

Sound like not a bad idea. It is just that 2 different people will be
inputting the info and the quote person does not need to enter
truck#,ticket#, and some other fields in. HOw can I handle this that the
quote person doesn't see those fields but the invoice person does??????

Thanks,
Barb

So why not just add a field named txtStatus or txtType and use that to
indicate if its a QUOTE or INVOICE?

[quoted text clipped - 40 lines]
Was this post helpful to you?

Why should I rate a post?

 

[darkroomdevil via AccessMonster.com]

I am sorry, I scanned quick and I thought you were re-asking how to make
controls visible or not visible. But the above does answer your original
question, so there you go ...

I guess I need to know if you are in agreements for your needs in having a
separate Invoice and Quote tables or if you are going to use one Invoice
table with a status field. If you could specify this and then list the steps
that you would need ... it would be less confusing to be on the same page for
what to do before we try to get in to the how to do.

Roger

 

[Guest]

I really want the two seperate forms. One for quotes and one for Invoices.
Re read above - having problems linking these tables. Currently have the
Invoices table would like to add a field to the invoice table called quoteid
as a drop down. If there is a quote for an order they can choose a quoteid
from the drop down and it will automatically fill in SOME of the fields. So
Invoice person does not have to.

Thanks for your follow up,
Barb

 

[darkroomdevil via AccessMonster.com]

Hi babs,

Just add a foreign key in your quote table = Invoice ID. Other than that the
tables are separate, each would have CustomerID, JobID ... whatever links the
each one needs for the info. To create an invoice from a quote you could put
a command button on the quote that runs an append query.

Roger

I really want the two seperate forms. One for quotes and one for Invoices.
Re read above - having problems linking these tables. Currently have the
Invoices table would like to add a field to the invoice table called quoteid
as a drop down. If there is a quote for an order they can choose a quoteid
from the drop down and it will automatically fill in SOME of the fields. So
Invoice person does not have to.

Thanks for your follow up,
Barb

I am sorry, I scanned quick and I thought you were re-asking how to make
controls visible or not visible. But the above does answer your original

[quoted text clipped - 7 lines]

Roger

 

[darkroomdevil via AccessMonster.com]

As an added thought, how does the user know whethter to look in the drop down
list for quotes or not?

In that scenario you are sitting in a new invoice anyway, so you might as
well do it from the quote form.

The only exception is if you are not talking about the quote /invoice but the
invoice sales detail, in other words line items of what they are purchasing
that are quoted for the customer that you wish to now ad to the invoice ...
just brain storming to make sure we are on the same page.

Roger

 

[Guest]

I think the command button for the append query may work. A few things
though my invoice # in the invoice table is not assigned until the report it
is based off of the date the report is printed out. My primary keys for the
invoice are Clientid and ticket# together. Plus when you are at the quote
pt. not sure if there will ever be an invoice generated for that quote.

Back to the append query. Not sure how to set that up. Do I make the
command button in design view of form(no wizard for this?) - go to the event
procedure of the on Click- Think I can just make the append query in the
query area- how do I Call it????is that the right word???


The user origanlly asked me to give them a drop down with quoteid so they
could pick it and it would fill in the fields- sounds like it should work but
not sure how????
Maybe they would get a list of quotes for the quote guy or the customer
would have their quoteid that they are actually now going through with and
they can pick it from the drop down. Would rather prefer doing it this way
if at all possible???????
Thanks again for your input.

 

[darkroomdevil via AccessMonster.com]

Hi babs,

I think the command button for the append query may work. A few things
though my invoice # in the invoice table is not assigned until the report it
is based off of the date the report is printed out.

which means that the record is not saved until printed?

My primary keys for the
invoice are Clientid and ticket# together. Plus when you are at the quote
pt. not sure if there will ever be an invoice generated for that quote.

If an invoice won't be generated from the quote, what is the quote for? So
many differences in how we run our different businesses I can see that I need
to get this simple

Back to the append query. Not sure how to set that up. Do I make the
command button in design view of form(no wizard for this?)

yes, you will learn more without the wizard

- go to the event
procedure of the on Click- Think I can just make the append query in the
query area- how do I Call it????is that the right word???

You can create a macro or use code, I would suggest code. Create the append
query, right click in the criteria row of the query of whatever field needs
to point to the quote form where the command button is and select 'build'.
Use the builder to find the control on the form to filter by.

Create a command button on the query form - bypass the wizard. In the
properties click event for the command button type [ and Access will fill in
the rest = [Event Procedure], click the build button to the right.

In the code behind forms screen enter this code to run your query

DoCmd.OpenQuery "YourQueriesName"

The user origanlly asked me to give them a drop down with quoteid so they
could pick it and it would fill in the fields- sounds like it should work but
not sure how????

Combo box, use the row source to defiine the table, query or SQL entered in
the Row Source. Make sure to define the column widths, column count and
bound column properties.

Maybe they would get a list of quotes for the quote guy or the customer
would have their quoteid that they are actually now going through with and
they can pick it from the drop down. Would rather prefer doing it this way
if at all possible???????

Sure, if it is up to the customer to give their quote id if they want to
implement the quote - or it is up to the customer to say 'I want to use my
quote' - then you could use a list box or combo box.

It sounds like a quote may be a 'pending order' more than a quote. We do
this for sales where the customer is in the process of finalizing what they
want by having in our invoice with these invoice fields:

CreateDate = Date invoice created
OrderDate = Only filed in when the invoice becomes a real order

Quotes are for quotes, not a sale at all that when printed has verbage as to
when the quote expires and such ...

I can see that this implementation may not work for you with the invoice
record being a compound key and not saved until printed, but you could save
it beore printing and enter the OrderDate when printed - just some thoughts

Roger

Thanks again for your input.

As an added thought, how does the user know whethter to look in the drop down
list for quotes or not?

[quoted text clipped - 8 lines]

Roger

 

[Guest]

Maybe I should start over somewhat: I really would like to have the dropdown
list for the quoteid be on the Ticket Entry Form and have it automatically
fill in SOME of the fields. Yes the quote is made to the customer and then
they decide yes they want to order the material and how much is added into
the invoice. See my lame attempt at re-explaining.


I have an existing invoice form(ticket entry form)(Invoice report generated
from this form)fields include the following

todays date
clientid(primary)
ticket#(primary)
servicedate
job#
comments
cost
taxrate
truck#
etc.
The Invoice# doesn't get put on until the report is printed.

Currently a step before this is a quote step that is done in excel and then
reentered as a record in the Ticket Entry form into Access if the customer
wants to go with a given quote.

These fields include a number of the fields from above but not all for
example not the truck#

The user would like to be able to have a dropdown list of quoteid within the
Ticket Entry form and have it FILL IN some of the fields automatically like
the cost, taxrate, etc.

I have added the quoteid as a lookup field to the ticket entry field thought
this would work to be the common field. however not all ticket entry records
are based off of previous quotes or vice versa tried to change the join to an
OUTER join but it doesn't like it and won't let me??? Thanks for your
patience and help.

Barb

Hi babs,

I think the command button for the append query may work. A few things
though my invoice # in the invoice table is not assigned until the report it
is based off of the date the report is printed out.

which means that the record is not saved until printed?

My primary keys for the
invoice are Clientid and ticket# together. Plus when you are at the quote
pt. not sure if there will ever be an invoice generated for that quote.

If an invoice won't be generated from the quote, what is the quote for? So
many differences in how we run our different businesses I can see that I need
to get this simple

Back to the append query. Not sure how to set that up. Do I make the
command button in design view of form(no wizard for this?)

yes, you will learn more without the wizard

- go to the event
procedure of the on Click- Think I can just make the append query in the
query area- how do I Call it????is that the right word???

You can create a macro or use code, I would suggest code. Create the append
query, right click in the criteria row of the query of whatever field needs
to point to the quote form where the command button is and select 'build'.
Use the builder to find the control on the form to filter by.

Create a command button on the query form - bypass the wizard. In the
properties click event for the command button type [ and Access will fill in
the rest = [Event Procedure], click the build button to the right.

In the code behind forms screen enter this code to run your query

DoCmd.OpenQuery "YourQueriesName"

The user origanlly asked me to give them a drop down with quoteid so they
could pick it and it would fill in the fields- sounds like it should work but
not sure how????

Combo box, use the row source to defiine the table, query or SQL entered in
the Row Source. Make sure to define the column widths, column count and
bound column properties.

Maybe they would get a list of quotes for the quote guy or the customer
would have their quoteid that they are actually now going through with and
they can pick it from the drop down. Would rather prefer doing it this way
if at all possible???????

Sure, if it is up to the customer to give their quote id if they want to
implement the quote - or it is up to the customer to say 'I want to use my
quote' - then you could use a list box or combo box.

It sounds like a quote may be a 'pending order' more than a quote. We do
this for sales where the customer is in the process of finalizing what they
want by having in our invoice with these invoice fields:

CreateDate = Date invoice created
OrderDate = Only filed in when the invoice becomes a real order

Quotes are for quotes, not a sale at all that when printed has verbage as to
when the quote expires and such ...

I can see that this implementation may not work for you with the invoice
record being a compound key and not saved until printed, but you could save
it beore printing and enter the OrderDate when printed - just some thoughts

Roger

Thanks again for your input.

As an added thought, how does the user know whethter to look in the drop down
list for quotes or not?

[quoted text clipped - 8 lines]

Roger

 

[darkroomdevil via AccessMonster.com]

Maybe I should start over somewhat: I really would like to have the dropdown
list for the quoteid be on the Ticket Entry Form and have it automatically
fill in SOME of the fields. Yes the quote is made to the customer and then
they decide yes they want to order the material and how much is added into
the invoice. See my lame attempt at re-explaining.

I have an existing invoice form(ticket entry form)(Invoice report generated
from this form)fields include the following

todays date
clientid(primary)
ticket#(primary)
servicedate
job#
comments
cost
taxrate
truck#
etc.
The Invoice# doesn't get put on until the report is printed.

Currently a step before this is a quote step that is done in excel and then
reentered as a record in the Ticket Entry form into Access if the customer
wants to go with a given quote.

These fields include a number of the fields from above but not all for
example not the truck#

The user would like to be able to have a dropdown list of quoteid within the
Ticket Entry form and have it FILL IN some of the fields automatically like
the cost, taxrate, etc.

I have added the quoteid as a lookup field to the ticket entry field thought
this would work to be the common field. however not all ticket entry records
are based off of previous quotes or vice versa tried to change the join to an
OUTER join but it doesn't like it and won't let me??? Thanks for your
patience and help.

Barb

Hi Barb,

Earlier I asked if you wanted both an invoice and quote tables - you
responded you wanted two forms? ....

I think before we go forward I need to know - what is your table structure?

Please post the SQL of the outer join so I can be sure what you are talking
about.

When you say ..

I have added the quoteid as a lookup field to the ticket entry field thought
this would work to be the common field. however not all ticket entry records
are based off of previous quotes or vice versa tried to change the join to an
OUTER join but it doesn't like it and won't let me??? Thanks for your
patience and help.

Which it? The Access query window? or an error on a form? or the info just
doesn't show up in the form? or when you run the query there are no records?

I would like us to slow down and work through the details.

Happy to help,
Roger

 

[Guest]

See table structures

Ticket Entry Form
todays date
clientid(primary)
ticket#(primary)
Added Quoteid as a lookup from Quotetable
servicedate
ticket#
comments
cost
taxrate
truck#
employeecode
truckhours
cartagerate
recordnumber
etc.


Quote table
quoteid(primary)
todaysdate
clientid
quotedate
job
grossweight
pit
cost
taxrate
cartagerate
recordnumber

Common field is the quoteid one(quotetable) to many(ticketentrytable)

Think I have the query working with the quoteid table and the ticketentry
table. I am able to go to quoteid dropdown and select it and it fills in the
given fields on the ticket entry table. One glitch is it puts in the
Clientid from the quotetable but I need reselect it for the ticketentry table
since it is one of the primary keys. Any way not to have to reselect the
clientid??

Another major problem is– the previous Ticket entry form for a new record
would fill in the autofill some of the fields from the previous record.

When I tie the working query onto the form it is fine if I delete the code
behind scenes for autofilling new record - but I would like for it to be able
to auto fill it.

The error I get is the following :
The current field must match the join key '?' in the table that serves as
the one side of one to many relationship. Enter a record in the 'one' side
table with the desired key value, and the make then make the entry with the
desired join key in the 'many' only' table.

Really don't need recordnumber on the quotetable. Deleted it and still get
the same error. Not sure why I am getting this error. When try and move onto
a new record(save it)

The field name to make the Autofill occur on the Ticket Entry form is see
below:
[Autofillnewrecords] is a text box Unbound on the form – not visible the
default value for the text box is listed below

"cbojob;ServiceDate;cost;Pit;Comments;GrossWeight;Truck;cboemplcode;TruckHours;CartageRate" These are the fields we want same as previous record.

An event procedure for the On Current of the Form

Private Sub Form_Current()
Me![Recordnumber] = Me.CurrentRecord
Call AutoFillNewRecord([Forms]![2TicketEntryForm])
End Sub

There is a field called recordnumber

I would like it to autofill info from previous record and then allow for the
user to select a quoteid if applicable and have it fill in those given fields
if the quoteid is selected. I think it is getting confused.

Thanks so much,
Barb

 

[darkroomdevil via AccessMonster.com]

Thanks for the detail, Some of it I am still unsure of, but the table
structure and some of your work flow I get now. Probably I don't need to
understand it if I can help you figure out what to do...

Think I have the query working with the quoteid table and the ticketentry
table. I am able to go to quoteid dropdown and select it and it fills in the
given fields on the ticket entry table. One glitch is it puts in the
Clientid from the quotetable but I need reselect it for the ticketentry table
since it is one of the primary keys. Any way not to have to reselect the
clientid??

Are you not quoting a customer, and then using the the quote for that
customer, for that customers new invoice? How would the clientID be
different?

You could just not include the ClientID in the fields that are brought in by
the quote. The ClientID in the invoice form would always be selected prior
to using the list of quotes.

The error I get is the following :
The current field must match the join key '?' in the table that serves as
the one side of one to many relationship. Enter a record in the 'one' side
table with the desired key value, and the make then make the entry with the
desired join key in the 'many' only' table.

One to many join, data needs to support the join. In other words the value
must exist in the one side before it can exist in the many side. Your record
is being saved by the auto fill process before this has happened.

The only relationships that exist that you have told me about where the
Invoice is on the many side are the ClientID and the quoteID fields. Which
means that you have entered a quotID that doesn't exist in the Quote table or
a ClientID that doesn't exist in the Client table. Neither of those make
sense but maybe this will help. Maybe put something like this in the
OnCurrent event code so you can test when it is being run to help you figure
it out ...

MsgBox "On current event ha+-ppened"

Maybe you have the composite key of ClientID and ticket# defined in a
relationship and the autofill function is saving your record before the
clientID field is filled with the ClientID?

[Autofillnewrecords] is a text box Unbound on the form – not visible the
default value for the text box is listed below

"cbojob;ServiceDate;cost;Pit;Comments;GrossWeight;Truck;cboemplcode;TruckHours;CartageRate" These are the fields we want same as previous record.

An event procedure for the On Current of the Form

Private Sub Form_Current()
Me![Recordnumber] = Me.CurrentRecord
Call AutoFillNewRecord([Forms]![2TicketEntryForm])
End Sub

Maybe change this to ...

Private Sub Form_Current()
If IsNull(Me.controlcboJob) then
Me![Recordnumber] = Me.CurrentRecord
Call AutoFillNewRecord([Forms]![2TicketEntryForm])
End If
End Sub

That way it is conditional that when the OnCurrent event triggers the code
only runs the first time the record is created. When you add from the quote
nothing will happen and you be able to edit the values without the autfill
being triggered in the future. Or create a field called newrecord that is
yes/no - defaults to yes, your caode changes it to no the first time autofill
runs and then thereafter does not run autofill if the value of newrecord is
no.

There is a field called recordnumber

I would like it to autofill info from previous record and then allow for the
user to select a quoteid if applicable and have it fill in those given fields
if the quoteid is selected. I think it is getting confused.

Thanks so much,
Barb

Hope this helps you figure it out.
Roger

 

[Guest]

Thinking that tying it to controlcbojob not the right text box. When the
user moves to a new record If the quoteid (cboquoteid) is NOT selected from
the drop down. Then autofill the specified fields for the new record. Maybe
I should tie the autofill to something other than the ON Current event. Not
sure what though? If keep it at ON current. when come in to new record at
first quote id is null until user selects it.

If tie it to afterupdate of cboquoteid - user may not go to cboquoteid at
all??


Thanks again for your help
Below from before:
Maybe change this to ...

Private Sub Form_Current()
If IsNull(Me.controlcboJob) then
Me![Recordnumber] = Me.CurrentRecord
Call AutoFillNewRecord([Forms]![2TicketEntryForm])
End If
End Sub

thanks,
Barb

Thanks for the detail, Some of it I am still unsure of, but the table
structure and some of your work flow I get now. Probably I don't need to
understand it if I can help you figure out what to do...

Think I have the query working with the quoteid table and the ticketentry
table. I am able to go to quoteid dropdown and select it and it fills in the
given fields on the ticket entry table. One glitch is it puts in the
Clientid from the quotetable but I need reselect it for the ticketentry table
since it is one of the primary keys. Any way not to have to reselect the
clientid??

Are you not quoting a customer, and then using the the quote for that
customer, for that customers new invoice? How would the clientID be
different?

You could just not include the ClientID in the fields that are brought in by
the quote. The ClientID in the invoice form would always be selected prior
to using the list of quotes.

The error I get is the following :
The current field must match the join key '?' in the table that serves as
the one side of one to many relationship. Enter a record in the 'one' side
table with the desired key value, and the make then make the entry with the
desired join key in the 'many' only' table.

One to many join, data needs to support the join. In other words the value
must exist in the one side before it can exist in the many side. Your record
is being saved by the auto fill process before this has happened.

The only relationships that exist that you have told me about where the
Invoice is on the many side are the ClientID and the quoteID fields. Which
means that you have entered a quotID that doesn't exist in the Quote table or
a ClientID that doesn't exist in the Client table. Neither of those make
sense but maybe this will help. Maybe put something like this in the
OnCurrent event code so you can test when it is being run to help you figure
it out ...

MsgBox "On current event ha+-ppened"

Maybe you have the composite key of ClientID and ticket# defined in a
relationship and the autofill function is saving your record before the
clientID field is filled with the ClientID?

[Autofillnewrecords] is a text box Unbound on the form – not visible the
default value for the text box is listed below

"cbojob;ServiceDate;cost;Pit;Comments;GrossWeight;Truck;cboemplcode;TruckHours;CartageRate" These are the fields we want same as previous record.

An event procedure for the On Current of the Form

Private Sub Form_Current()
Me![Recordnumber] = Me.CurrentRecord
Call AutoFillNewRecord([Forms]![2TicketEntryForm])
End Sub

Maybe change this to ...

Private Sub Form_Current()
If IsNull(Me.controlcboJob) then
Me![Recordnumber] = Me.CurrentRecord
Call AutoFillNewRecord([Forms]![2TicketEntryForm])
End If
End Sub

That way it is conditional that when the OnCurrent event triggers the code
only runs the first time the record is created. When you add from the quote
nothing will happen and you be able to edit the values without the autfill
being triggered in the future. Or create a field called newrecord that is
yes/no - defaults to yes, your caode changes it to no the first time autofill
runs and then thereafter does not run autofill if the value of newrecord is
no.

There is a field called recordnumber

I would like it to autofill info from previous record and then allow for the
user to select a quoteid if applicable and have it fill in those given fields
if the quoteid is selected. I think it is getting confused.

Thanks so much,
Barb

Hope this helps you figure it out.
Roger

 

[darkroomdevil via AccessMonster.com]

Thinking that tying it to controlcbojob not the right text box. When the
user moves to a new record If the quoteid (cboquoteid) is NOT selected from
the drop down. Then autofill the specified fields for the new record. Maybe
I should tie the autofill to something other than the ON Current event. Not
sure what though? If keep it at ON current. when come in to new record at
first quote id is null until user selects it.

If tie it to afterupdate of cboquoteid - user may not go to cboquoteid at
all??

Thanks again for your help
Below from before:
Maybe change this to ...

Private Sub Form_Current()
If IsNull(Me.controlcboJob) then
Me![Recordnumber] = Me.CurrentRecord
Call AutoFillNewRecord([Forms]![2TicketEntryForm])
End If
End Sub

thanks,
Barb

I noticed in help theat there is a NewRecord property, maybe you could use
that -

Or you could use code to set the default value as needed.