Default value in Form field (1 Viewer)

Siegfried

Registered User.
Local time
Today, 18:20
Joined
Sep 11, 2014
Messages
105
Hi. I could be wrong, but I think the original question about Default Value is solved. I think this is about Cascading Combobox now.
Hi DBguy, Any suggetions on this concatention issue?
Thanks.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:20
Joined
Jan 23, 2006
Messages
15,364
Siegfried,
Is your latest database still the one attached to post #32?
Also, your form is called PurchaaseOrderItemsFSub was that intended to be a subform within PurchaseOrderF??
I'd like to see a scenario that starts with a selection and proceeds through to the response/answer you are expecting. Then, we can be looking at and testing the same thing.
 

Siegfried

Registered User.
Local time
Today, 18:20
Joined
Sep 11, 2014
Messages
105
Siegfried,
Is your latest database still the one attached to post #32?
Also, your form is called PurchaaseOrderItemsFSub was that intended to be a subform within PurchaseOrderF??
I'd like to see a scenario that starts with a selection and proceeds through to the response/answer you are expecting. Then, we can be looking at and testing the same thing.

Yes it is.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:20
Joined
Jan 23, 2006
Messages
15,364
I was editing my last post when you responded. I think your yes it is refers to the latest database.
What about the form and subform? It seems your subform is independent in the database, but typically we see a subform control on the main form (PurchaseOrderF here).
In your Exchange rates the # 8 shows EUR but the rate is 1------doesn't seem correct to me????

When I review your PurchaseOrders and PurchaseorderItem in this query
(set up so I can relat items/orders to your form/subform info)
Code:
SELECT PurchaseOrderT.POID
    ,PurchaseOrderItemT.POItemID
    ,PurchaseOrderT.PONumber
    ,PurchaseOrderItemT.ProductID
    ,PurchaseOrderItemT.ProductModel
    ,PurchaseOrderItemT.UnitPrice
    ,Count(PurchaseOrderItemT.Quantity) AS CountOfQuantity
    ,PurchaseOrderItemT.CurrencyID
    ,CurrencyT.CurrencyName
    ,ExchangeRateT.ExchangeRate
    ,ExchangeRateT.ExchangeRateDate
FROM (
    (
        PurchaseOrderT INNER JOIN PurchaseOrderItemT
            ON PurchaseOrderT.POID = PurchaseOrderItemT.POID
        ) INNER JOIN ExchangeRateT
        ON (PurchaseOrderItemT.ExchangeRateID = ExchangeRateT.ExchangeRateID)
            AND (PurchaseOrderItemT.ExchangeRateID = ExchangeRateT.ExchangeRateID)
    )
INNER JOIN CurrencyT
    ON PurchaseOrderItemT.CurrencyID = CurrencyT.CurrencyID
GROUP BY PurchaseOrderT.POID
    ,PurchaseOrderItemT.POItemID
    ,PurchaseOrderT.PONumber
    ,PurchaseOrderItemT.ProductID
    ,PurchaseOrderItemT.ProductModel
    ,PurchaseOrderItemT.UnitPrice
    ,PurchaseOrderItemT.CurrencyID
    ,CurrencyT.CurrencyName
    ,ExchangeRateT.ExchangeRate
    ,ExchangeRateT.ExchangeRateDate
    ,ExchangeRateT.ExchangeRateDate;

I see this, is this what you expect just from related tables?

On Order2 1 MX3 has Exch .8054, and the other .5084 ----correct?

QJEDOrder_Items_Exch

POIDPOItemIDPO numberproductmodelunit priceCountOfQuantityCurrencyIDcurrencyRoE:Date:
1​
7​
AEANT-2020.001
8​
X40
45.00​
1​
1​
EUR
0.9162​
01-Mar-20​
1​
9​
AEANT-2020.001
18​
T5
250.00​
1​
4​
USD
1​
01-Jan-20​
2​
3​
AEANT-2020.0002
6​
MX3
99.00​
1​
1​
EUR
0.8054​
01-Feb-20​
2​
6​
AEANT-2020.0002
3​
T24i
230.00​
1​
1​
EUR
0.9162​
01-Mar-20​
2​
8​
AEANT-2020.0002
6​
MX3
99.00​
1​
1​
EUR
0.5804​
01-Apr-20​
3​
2​
AEANT-2020.0003
2​
M720q
1,200.00​
1​
1​
EUR
0.8423​
01-Jan-20​
3​
4​
AEANT-2020.0003
24​
X500
150.00​
1​
1​
EUR
0.9162​
01-Mar-20​
3​
5​
AEANT-2020.0003
4​
Qwerty US
20.00​
1​
1​
EUR
0.5804​
01-Apr-20​
 
Last edited:

Siegfried

Registered User.
Local time
Today, 18:20
Joined
Sep 11, 2014
Messages
105
I was editing my last post when you responded. I think your yes it is refers to the latest database.
What about the form and subform? It seems your subform is independent in the database, but typically we see a subform control on the main form (PurchaseOrderF here).
In your Exchange rates the # 8 shows EUR but the rate is 1------doesn't seem correct to me????

When I review your PurchaseOrders and PurchaseorderItem in this query
(set up so I can relat items/orders to your form/subform info)
Code:
SELECT PurchaseOrderT.POID
    ,PurchaseOrderItemT.POItemID
    ,PurchaseOrderT.PONumber
    ,PurchaseOrderItemT.ProductID
    ,PurchaseOrderItemT.ProductModel
    ,PurchaseOrderItemT.UnitPrice
    ,Count(PurchaseOrderItemT.Quantity) AS CountOfQuantity
    ,PurchaseOrderItemT.CurrencyID
    ,CurrencyT.CurrencyName
    ,ExchangeRateT.ExchangeRate
    ,ExchangeRateT.ExchangeRateDate
FROM (
    (
        PurchaseOrderT INNER JOIN PurchaseOrderItemT
            ON PurchaseOrderT.POID = PurchaseOrderItemT.POID
        ) INNER JOIN ExchangeRateT
        ON (PurchaseOrderItemT.ExchangeRateID = ExchangeRateT.ExchangeRateID)
            AND (PurchaseOrderItemT.ExchangeRateID = ExchangeRateT.ExchangeRateID)
    )
INNER JOIN CurrencyT
    ON PurchaseOrderItemT.CurrencyID = CurrencyT.CurrencyID
GROUP BY PurchaseOrderT.POID
    ,PurchaseOrderItemT.POItemID
    ,PurchaseOrderT.PONumber
    ,PurchaseOrderItemT.ProductID
    ,PurchaseOrderItemT.ProductModel
    ,PurchaseOrderItemT.UnitPrice
    ,PurchaseOrderItemT.CurrencyID
    ,CurrencyT.CurrencyName
    ,ExchangeRateT.ExchangeRate
    ,ExchangeRateT.ExchangeRateDate
    ,ExchangeRateT.ExchangeRateDate;

I see this, is this what you expect just from related tables?

On Order2 1 MX3 has Exch .8054, and the other .5084 ----correct?

QJEDOrder_Items_Exch

POIDPOItemIDPO numberproductmodelunit priceCountOfQuantityCurrencyIDcurrencyRoE:Date:
1​
7​
AEANT-2020.001
8​
X40
45.00​
1​
1​
EUR
0.9162​
01-Mar-20​
1​
9​
AEANT-2020.001
18​
T5
250.00​
1​
4​
USD
1​
01-Jan-20​
2​
3​
AEANT-2020.0002
6​
MX3
99.00​
1​
1​
EUR
0.8054​
01-Feb-20​
2​
6​
AEANT-2020.0002
3​
T24i
230.00​
1​
1​
EUR
0.9162​
01-Mar-20​
2​
8​
AEANT-2020.0002
6​
MX3
99.00​
1​
1​
EUR
0.5804​
01-Apr-20​
3​
2​
AEANT-2020.0003
2​
M720q
1,200.00​
1​
1​
EUR
0.8423​
01-Jan-20​
3​
4​
AEANT-2020.0003
24​
X500
150.00​
1​
1​
EUR
0.9162​
01-Mar-20​
3​
5​
AEANT-2020.0003
4​
Qwerty US
20.00​
1​
1​
EUR
0.5804​
01-Apr-20​

Hi jdraw,

Thanks againg for helping out and spending your time on this.
"Yes it is", sorry that was a bit short indeed.
I haven't created the subform yet on the Main form PurchaseOrderF.
Form PurchaseOrderF will indeed get a subfom: PurchaseOrderItemsFSub.
However I was first coverting the FK fields to comboboxes and wanted to concatenate the CurrencyID combo box with the ExchangeRateID combobox. But I am stil struggling with that one.
When I scroll between the records, the ExchangeRateID field remains empty when USD is selected in the above CurrencyID field, when I select the field I can select the exchange rates though. Something wrong with the requery and combo dropdown. Trying to get the ExchangeRateID combobox to drop down only when it has the focus (selected) and not when scrolling between records.
I am working with two bound comboboxes.... all examples I found on cascading use unbound combo boxes... perhaps that's the issue? But if that's the case then I don't how to combine the CurrencyID and ExchangeRateID on the form?
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:20
Joined
Jan 23, 2006
Messages
15,364
Hmmm? I'm not sure how to answer that --it could be anything if you found some code somewhere. However, I'm curious as to why you show the EchangeRateID and not the Rate and Date. It seems more hidden and if I were using the form/subform, I think I'd like to see the Rate and Date for this currency --but I'm not using it so you and users must decide.

You want the exchange rate to be associated with the received date, not the OrderDate correct? Is that a rule that you can program? I realize your current approach is flexibility that you can/code update.
Just trying to get you to say how "frozen" your approach/design/business rules are.


I notice your POnumbers have different number of 000 after the dot "." --is that a typo or significant?
 

Siegfried

Registered User.
Local time
Today, 18:20
Joined
Sep 11, 2014
Messages
105
Hmmm? I'm not sure how to answer that --it could be anything if you found some code somewhere. However, I'm curious as to why you show the EchangeRateID and not the Rate and Date. It seems more hidden and if I were using the form/subform, I think I'd like to see the Rate and Date for this currency --but I'm not using it so you and users must decide.

You want the exchange rate to be associated with the received date, not the OrderDate correct? Is that a rule that you can program? I realize your current approach is flexibility that you can/code update.
Just trying to get you to say how "frozen" your approach/design/business rules are.


I notice your POnumbers have different number of 000 after the dot "." --is that a typo or significant?

Actually that's a good remark. I didn't consider showing the rate and the date for this currency... I somehow wanted to combine this in one field.
I'm not associating the exchange rate with the receive date as I want to be able to select a currency rate manually.
An order I place on the 25th of March, will start with the ExchangeRate of March 2020 but if the invoice arrives 30/03 let's say, then accounts will book the cost for April and I will adjust the ExchangeRate of this order to April 2020. That why I'm not linking the ExchangeRate with the order or receive date. I do set a default being the ExchangeRate of the month the order is made but I keep the option to change this by selecting another value from the combobox if so required. Hope this make a more sense?
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:20
Joined
Jan 23, 2006
Messages
15,364
Siegfried,
I realize you are designing and testing components.
Do you have a detailed requirement list, or a list of business rules that the database should support.
What exactly is your role in this project/company? Is there a project development and implementation plan? Do you have some tester /user/focus groups to assist your efforts?
Just me being a little nosy --trying to get some context.
A little soap box here ---focus on WHAT is required, then determine HOW it might be implemented by reviewing some options.
Good luck.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:20
Joined
Jan 23, 2006
Messages
15,364
Siegfried,
I did a little experimenting. I put copies of your form and subform together
(note the J in the form names)
see: MockupOfFormSubform.png
Then a combo box (combo14) and a text box (text16).
see MockupComboSelected.png
The combo rowsource fo testing the concept is
Code:
SELECT CurrencyT.CurrencyName
, ExchangeRateT.ExchangeRateDate
, ExchangeRateT.ExchangeRate
FROM ExchangeRateT INNER JOIN CurrencyT ON
(ExchangeRateT.CurrencyID = CurrencyT.CurrencyID) 
WHERE (((CurrencyT.CurrencyName)= "EUR"));

and for testing I added a msgbox to show what was happening
see MockupComboWithMessageForTesting.png

This is a mockup the combo rowsource is made up.

The png files are in the attached zip.

I think you should review the requirements and firm them up a little, then have readers propose at some options for HOW to do it.

Feedback is welcome.
Good luck.
 

Attachments

  • ForSiegfried.zip
    185.3 KB · Views: 149

Siegfried

Registered User.
Local time
Today, 18:20
Joined
Sep 11, 2014
Messages
105
Siegfried,
I realize you are designing and testing components.
Do you have a detailed requirement list, or a list of business rules that the database should support.
What exactly is your role in this project/company? Is there a project development and implementation plan? Do you have some tester /user/focus groups to assist your efforts?
Just me being a little nosy --trying to get some context.
A little soap box here ---focus on WHAT is required, then determine HOW it might be implemented by reviewing some options.
Good luck.
Hi jdraw,

Yes, you are correct, I'm trying to setup my own database to get rid of my dozens of excel files and simplify my work.
At my company I'm the IT coordinator (only me: 69 staff), I report to our head office and get all directives and instructions from them to implement.
Unfortunately though, we don't have a software program in place to manage all the IT matters, so everything I do and everything I have to report, I'm doing using excel...!
I keep track of the assests, the users, the budget, the purchase orders, passwords, IP address, software licenses, computernames and so on.
As a database expert you will understand that using all these excel files isn't ideal: the same data is repeatedly being entered in the various other excel files
and the more there are, the more difficult it is to keep them up-to-date.
Quite often I get a request from head office to fill out a new excel format and a few weeks later I get kind of the same request from some else
in a different layout with an additonal column... I have muliple excels from various managers with kind of the same data which I have to keep up-to-date.
So, my idea is to put all of this in Access. I will be the only user using the database at first, I might give acces (read only) to the 3 senior
managers (the director (reports) , HR manager (EmployeeF) and the Chief Accountant (BudetF, PurchaseOrderF) but still thinking about that, I will make sure though that I can generate reports in pdf and excel for any data from the database.
I do have a plan and a design.
To give you a rough idea, I want to process the following data in the database:
Users and Employees, IT budget per year, IT purchases, Budget spent and remaining, Assets, all equipment: computers, monitors, servers, routers, switches, printers, smartphones,
digital camera's. Computernames, which user has which asset, subscriptions & leasing, software & applications, license keys, telephone numbers, passwords. Which user is using which software...
I started with the Users table and Empolyee table. Now I'm focusing on the Purchase Order table which I next need to link to the annual budget to draw reports and then the assets table and hardware table (not every hardware is an asset, computers are an asset, mouse, keyboards for example isn't) link them to a user and keep track when to replace them....
Quite a lot to do as you can see.
I'll be honest that I'm trying already for a long time, it's a slow process. I doing this in my free time (don't have the time at work, already overloaded, just me on IT) in the hope I can make my work a bit smoother and stop wasting my time on all these excels files of which I'm starting to loose track...
I'm not a database expert, but I like playing with access and trying to create this, but I have to rely on you experts at times to help me out when I get stuck. And I am very
grateful that forums like these exist where experts make time to help and share their knowledge. The many books and video tutorials do get you on your way but many things
you don't find or you can't figure out yourself. Too often an example found doesn't match with what you actually want to do and you get stuck.

Best regards,
Siegfried
 

Siegfried

Registered User.
Local time
Today, 18:20
Joined
Sep 11, 2014
Messages
105
Siegfried,
I did a little experimenting. I put copies of your form and subform together
(note the J in the form names)
see: MockupOfFormSubform.png
Then a combo box (combo14) and a text box (text16).
see MockupComboSelected.png
The combo rowsource fo testing the concept is
Code:
SELECT CurrencyT.CurrencyName
, ExchangeRateT.ExchangeRateDate
, ExchangeRateT.ExchangeRate
FROM ExchangeRateT INNER JOIN CurrencyT ON
(ExchangeRateT.CurrencyID = CurrencyT.CurrencyID)
WHERE (((CurrencyT.CurrencyName)= "EUR"));

and for testing I added a msgbox to show what was happening
see MockupComboWithMessageForTesting.png

This is a mockup the combo rowsource is made up.

The png files are in the attached zip.

I think you should review the requirements and firm them up a little, then have readers propose at some options for HOW to do it.

Feedback is welcome.
Good luck.
Hi jdraw,

Yes, that a much more clever approach then what I was trying.
It indeed makes more sense this way, less complicated than my approach.
Thanks I'll try this out.

(y) :)
Best regards,
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:20
Joined
Jan 23, 2006
Messages
15,364
Well that was just a demo for showing the concept. It's a mockup. I think you should identify the scope for your specific project and design accordingly. It isn't that what I suggested was more clever, it's just that I was/am not certain of what you are trying to achieve. In order to understand the codes on your subform, I created a query to show me the related tables and the names of those codes. I used that to adjust things and to be clear of what each thing meant.
You seem to be a very busy person, wearing many hats. So it's important to set some priorities and determine the project you want to address first. (perhaps you have done that, and if so, I think you need some specific things in your requirements). I am not trying to dissuade you. Think of the things that you would have to tell a developer, or think of what you would need if someone(boss) said I want you to build us an IT Inventory database. The typical questions (what exactly, for whom, who can I draw on for details, when is it expected, .....).
There are a number of articles in the Database Planning and Design link in my signature that can be helpful. Everything from concepts, to vba, to testing... and the BA-Expert nuggets are great for analysis ideas.
I have a suspicion that you have focused on a particular aspect of physical Access and just need to step back and re-look at the bigger picture.
In many organizations, there has been recognition that multiple spreadsheets are not sufficient for data storage/acquisition and management. Several organizations of 60+ people has some sort of ERP as well as an IT group or contracted group.
Anyway, members here will help you with questions and offer advice.
If there is more with this project just ask clear questions, or provide a database or sample of the work and you will get responses.

I'm going to suggest this video from Software Matters of the development of a Stock Management system. It may help with scope and priorities and the steps/things involved.
Good luck
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 13:20
Joined
Jan 23, 2006
Messages
15,364
Another thing you can do to help with analysis and to confirm your understanding of the requirement is to build a "demonstration prototype". Something with a few forms and buttons that hints to the overview of the project and some of the major components. The buttons do nothing more than change from one form to another or pops up some message of what happens here, or ho is responsible for something, or signifies some status change or event. It doesn't do much in detail, but it shows the logic flow and some significant steps/stages in the overall project.
eg. Consider a loan/grant application ( Section A)
Applicant is entered into the system
Details gathered
Msg re insufficient info
Application reviewed
Status updated
Preliminary project info supplied
Vet the supplied info
Review is successful or Review identifies issues an return to statusX
If successful, identify loan/grant conditions make offer to applicant
Applicant accepts/rejects/wants to negotiate
Status updated
End of this section A of prototype

This sort of thing is used to get feed back, clarify business process and requirements.

Let us know how your project progresses. We're here if you have questions.
Good luck.
 
Last edited:

Siegfried

Registered User.
Local time
Today, 18:20
Joined
Sep 11, 2014
Messages
105
Another thing you can do to help with analysis and to confirm your understanding of the requirement is to build a "demonstration prototype". Something with a few forms and buttons that hints to the overview of the project and some of the major components. The buttons do nothing more than change from one form to another or pops up some message of what happens here, or ho is responsible for something, or signifies some status change or event. It doesn't do much in detail, but it shows the logic flow and some significant steps/stages in the overall project.
eg. Consider a loan/grant application ( Section A)
Applicant is entered into the system
Details gathered
Msg re insufficient info
Application reviewed
Status updated
Preliminary project info supplied
Vet the supplied info
Review is successful or Review identifies issues an return to statusX
If successful, identify loan/grant conditions make offer to applicant
Applicant accepts/rejects/wants to negotiate
Status updated
End of this section A of prototype

This sort of thing is used to get feed back, clarify business process and requirements.

Let us know how your project progresses. We're here if you have questions.
Good luck.
thanks for all the useful info. (y)
 

Users who are viewing this thread

Top Bottom