Billing form (1 Viewer)

Bryan Rousseau · Mar 4, 2018

Hey guys,

Scope : I have a client table and a activity table, so each client can have different activity

Problem: I need to create a billing form, where we would just enter the serial number of the activity and all the information about the client and the specific activity would fill in. But I managed to do so but when I enter a serial number that was not created in the activity table I don't know how to make it so it creates a new table where if not found in the activity table we have the option of filling the information in the billing table.

hope my question is clear !

have a great day !

Ranman256 · Mar 4, 2018

the billing form, there'd be a combo box for you to pick the Client.
in the AFTER UPDATE event, youd run an update query to update all the Billing fields from the Client fields.

you can do the same with Activity ID.
run update query.

Bryan Rousseau · Mar 4, 2018

Ok thanks.

Cause the way I was trying to do it was by creating a new numerical field and make the contend select the activity ID then select the activity serial number. I know now that this isn’t the way to go but why ?

How would you go about creating the combo field ?

How I see it: create all the billing field that can’t be found in the activity table in a new billing table, then make a query combining both table so when I enter the serial number it fills out all the activity fields then I just have to fill the billing fields.

Hope to ear from you soon !

mike60smart · Mar 4, 2018

Hi Bryan

Can you upload a zipped copy of the Db??

Minty · Mar 5, 2018

You don't normally make new tables on the fly , this would tend to indicate a poor design choice.

Can you post a picture of the table design - from the relationship screen is a good place, or as Mike suggested upload the db with some sample data, and maybe an excel sheet of what you are trying to get to ?

Bryan Rousseau · Mar 11, 2018

Minty said:
You don't normally make new tables on the fly , this would tend to indicate a poor design choice.

Can you post a picture of the table design - from the relationship screen is a good place, or as Mike suggested upload the db with some sample data, and maybe an excel sheet of what you are trying to get to ?

Sent from my iPhone using Tapatalk

Bryan Rousseau · Mar 11, 2018

mike60smart said:
Hi Bryan

Can you upload a zipped copy of the Db??

A zipped copy will be harder to send because it sits on a closed network with many restriction... I’ll see if I can publish a version with no data in soon

Sent from my iPhone using Tapatalk

Minty · Mar 12, 2018

Before getting into anything too much further, fix those field names starting with %,
you should avoid spaces and any special characters in field names, and don't start a field name with a number.

The % symbol is a wild card in SQL server so would cause all manner of issues.
Also give your ID fields a meaninful name otherwise you'll never remember which ID field you are referring to.

You appear to be storing a lot of dates in one table, unfortunately my French is very limited, so I can't be certain, but often you would be better having a table of possible actions and store the action and date as a related table. That way you are only storing what has happened, and have a clear trail of actions and dates that can be easily added to if/when a process is altered.

Again this could be a translation issue but your final table looks like it is a summary of caution table. I would simply flag the final record in the caution table if it contains the information you require. Generally it is considered bad practice to duplicate data.

Bryan Rousseau · Mar 12, 2018

Minty said:
Before getting into anything too much further, fix those field names starting with %,
you should avoid spaces and any special characters in field names, and don't start a field name with a number.

The % symbol is a wild card in SQL server so would cause all manner of issues.
Also give your ID fields a meaninful name otherwise you'll never remember which ID field you are referring to.

You appear to be storing a lot of dates in one table, unfortunately my French is very limited, so I can't be certain, but often you would be better having a table of possible actions and store the action and date as a related table. That way you are only storing what has happened, and have a clear trail of actions and dates that can be easily added to if/when a process is altered.

Again this could be a translation issue but your final table looks like it is a summary of caution table. I would simply flag the final record in the caution table if it contains the information you require. Generally it is considered bad practice to duplicate data.

Enventually, the table cautionnement finaux is going to be replace by the table listing - facturation (which it translate to the billing table), and I want to do so because the cautionnement finaux table duplicates information that already exist.

I’ll try to upload a zip file soon.

Thanks for the heads up about the % in my field names I’ll correct it promptly. For the spacing in the field name I was under the impression that if you put brackets [ ] it would solve this issue.

Have a great day !

Minty · Mar 12, 2018

The brackets will, but the additional typing and scope for harder to spot errors is not worth the effort.
If you are doing it for meaningful labels or query headers, then set the caption property on the field in the table and it will use that.

Billing form (1 Viewer)

Bryan Rousseau

New member

Ranman256

Well-known member

Bryan Rousseau

New member

mike60smart

Registered User.

Minty

AWF VIP

Bryan Rousseau

New member

Bryan Rousseau

New member

Minty

AWF VIP

Bryan Rousseau

New member

Minty

AWF VIP

Similar threads

Users who are viewing this thread