If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Can I populate a control with the record number?
hi Tony,
On 21.02.2010 12:26, TonyWilliams via AccessMonster.com wrote: Sorry Stefan I'm a bit of a newbie. Where would I find the Record Source Property? The Record Source property is a property of the Form. Open your form in the design view, press F4 to open the property editor. Select your form and navigate to the Data page. mfG -- stefan -- |
#12
|
|||
|
|||
Can I populate a control with the record number?
hi Tony,
On 21.02.2010 12:40, TonyWilliams via AccessMonster.com wrote: Sorry Stefan I've just realised where you mean. But, if I put it in the Record Source of the form how will my form get access to all the other fields in the table on which the form is based and which is currently the Record Source of the form? Built a sample table: myTable: ID, AutoNumber, Primary Key Payload, Text Fill in some records. Delete one or three. Create a sample query on it, use this SQL: SELECT myTable.ID, myTable.Payload, DCount("*","myTable","ID=" & [ID]) AS RecNo FROM myTable; Create a new query, don't select a table in the dialog. Switch to the SQL view and paste it into it. Now you can use this query as Record Source in your form. Instead of using a query you may copy this SQL string into the Record Source of a new form. mfG -- stefan -- |
#13
|
|||
|
|||
Can I populate a control with the record number?
Thanks Stefan I think I get the idea (?) I'll have a go over the next day or
so and come back if I have a problem. Duty (my wife!) calls now! Thanks for sticking with me Tony Stefan Hoffmann wrote: hi Tony, Sorry Stefan I've just realised where you mean. But, if I put it in the Record Source of the form how will my form get access to all the other fields in the table on which the form is based and which is currently the Record Source of the form? Built a sample table: myTable: ID, AutoNumber, Primary Key Payload, Text Fill in some records. Delete one or three. Create a sample query on it, use this SQL: SELECT myTable.ID, myTable.Payload, DCount("*","myTable","ID=" & [ID]) AS RecNo FROM myTable; Create a new query, don't select a table in the dialog. Switch to the SQL view and paste it into it. Now you can use this query as Record Source in your form. Instead of using a query you may copy this SQL string into the Record Source of a new form. mfG -- stefan -- -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201002/1 |
#14
|
|||
|
|||
Can I populate a control with the record number?
Ok so I think I followed your instructions and have an sql statement like
this SELECT tblhvdealspt1.ID, tblhvdealspt1.txtdn, DCount("*","tblhvdealspt1", "ID=" & [ID]) AS RecNo FROM tblhvdealspt1; I used this as the Record source of my form and all the controls are now showing as having no control source. I'm sorry to be so pedantic but could I just return to what I'm trying to do. I have a form that has a control for a company name (txtcompany) It has a subform where I input data on a number of deals which are identified by a control txtdealnbr. In any month any company can have many deals so the first deal for the first company is 1, then 2, then 3 and so on. The next company can also have a number of deals and they will be 1 then 2 then 3 etc. I'm still not sure how using the ID of the table creates these for each of the deals as they are held in the table and the ID will run as 1,2,3,4,5 and 6 in my example. Each month I want each companies deal number to start as 1 not consecutive from the previous month. This why I can't see how using the ID will do this. But I am not an experienced Access programmer so maybe I'm missing something fundamental here? Thanks again Stefan Tony Stefan Hoffmann wrote: hi Tony, Sorry Stefan I've just realised where you mean. But, if I put it in the Record Source of the form how will my form get access to all the other fields in the table on which the form is based and which is currently the Record Source of the form? Built a sample table: myTable: ID, AutoNumber, Primary Key Payload, Text Fill in some records. Delete one or three. Create a sample query on it, use this SQL: SELECT myTable.ID, myTable.Payload, DCount("*","myTable","ID=" & [ID]) AS RecNo FROM myTable; Create a new query, don't select a table in the dialog. Switch to the SQL view and paste it into it. Now you can use this query as Record Source in your form. Instead of using a query you may copy this SQL string into the Record Source of a new form. mfG -- stefan -- -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201002/1 |
#15
|
|||
|
|||
Can I populate a control with the record number?
hi Tony,
On 21.02.2010 14:38, TonyWilliams via AccessMonster.com wrote: I'm sorry to be so pedantic but could I just return to what I'm trying to do. I have a form that has a control for a company name (txtcompany) It has a subform where I input data on a number of deals which are identified by a control txtdealnbr. In any month any company can have many deals so the first deal for the first company is 1, then 2, then 3 and so on. The next company can also have a number of deals and they will be 1 then 2 then 3 etc. I'm still not sure how using the ID of the table creates these for each of the deals as they are held in the table and the ID will run as 1,2,3,4,5 and 6 in my example. Each month I want each companies deal number to start as 1 not consecutive from the previous month. This why I can't see how using the ID will do this. But I am not an experienced Access programmer so maybe I'm missing something fundamental here? No, I don't think so. Take a closer look at the logic in the simple example. The record number is built on an (artifical) order given through the criteria in the DCount() statement. You have now to build a criteria which expresses exactly your needs. Using a simplified table structu Company: [ID], AutoNumber, Primary Key [Name] Text(255) Not Null Deal: [ID], AutoNumber, Primary Key [idCompany], Number Not Null, Foreign Key to table Company [Date] DateTime Not Null [Comment] Memo Then you need for your sub-form this SQL as record source: SELECT *, DCount( "*", "[Deal]", "[idCompany] = " & [idCompany] & " AND [Date] = " & [Date] & " AND Year([Date]) = " & Year([Date]) & " AND Month([Date]) = " & Month([Date]) ) AS [RecNo] FROM [Deal] mfG -- stefan -- |
#16
|
|||
|
|||
Can I populate a control with the record number?
Thanks Stefan, forgive me but I've been at this all day now and my 65 year
old brain (which doesn't work as quick as it used to) is getting a bit weary. I'm going to study your reply closer tomorrow when the fog's lifted from my eyes! Thanks agian really appreciate your efforts. Tony Stefan Hoffmann wrote: hi Tony, I'm sorry to be so pedantic but could I just return to what I'm trying to do. I have a form that has a control for a company name (txtcompany) It has a [quoted text clipped - 7 lines] 1 not consecutive from the previous month. This why I can't see how using the ID will do this. But I am not an experienced Access programmer so maybe I'm missing something fundamental here? No, I don't think so. Take a closer look at the logic in the simple example. The record number is built on an (artifical) order given through the criteria in the DCount() statement. You have now to build a criteria which expresses exactly your needs. Using a simplified table structu Company: [ID], AutoNumber, Primary Key [Name] Text(255) Not Null Deal: [ID], AutoNumber, Primary Key [idCompany], Number Not Null, Foreign Key to table Company [Date] DateTime Not Null [Comment] Memo Then you need for your sub-form this SQL as record source: SELECT *, DCount( "*", "[Deal]", "[idCompany] = " & [idCompany] & " AND [Date] = " & [Date] & " AND Year([Date]) = " & Year([Date]) & " AND Month([Date]) = " & Month([Date]) ) AS [RecNo] FROM [Deal] mfG -- stefan -- -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via http://www.accessmonster.com |
#17
|
|||
|
|||
Can I populate a control with the record number?
Hi Stefan.
So, this is what I have as the Record Source for my form: SELECT DCount("*","[tblhvdealspt1]","[IDcompany] = " & [IDcompany] & " AND [txtmonth] = " & [txtmonth] & " AND Year([txtmonth]) = " & Year([txtmonth]) & " AND Month([txtmonth]) = " & Month([txtmonth])) AS RecNo, *; And I created an Unbound Control on my form with the name RecNo. Should I have done this or was RecNo supposed to have been the field that I'm storing which is Txtdealnbr, which I can't make it that as it is available as a Control Source. AND all the other controls on my form have the signal that they have no Control Source and when I click on "Add Existing Fields" only RecNo is shown. What have I done wrong? Should I add the tablhvdealspt1 in the query design screen? Thanks again for your continued help. Tony Stefan Hoffmann wrote: hi Tony, I'm sorry to be so pedantic but could I just return to what I'm trying to do. I have a form that has a control for a company name (txtcompany) It has a [quoted text clipped - 7 lines] 1 not consecutive from the previous month. This why I can't see how using the ID will do this. But I am not an experienced Access programmer so maybe I'm missing something fundamental here? No, I don't think so. Take a closer look at the logic in the simple example. The record number is built on an (artifical) order given through the criteria in the DCount() statement. You have now to build a criteria which expresses exactly your needs. Using a simplified table structu Company: [ID], AutoNumber, Primary Key [Name] Text(255) Not Null Deal: [ID], AutoNumber, Primary Key [idCompany], Number Not Null, Foreign Key to table Company [Date] DateTime Not Null [Comment] Memo Then you need for your sub-form this SQL as record source: SELECT *, DCount( "*", "[Deal]", "[idCompany] = " & [idCompany] & " AND [Date] = " & [Date] & " AND Year([Date]) = " & Year([Date]) & " AND Month([Date]) = " & Month([Date]) ) AS [RecNo] FROM [Deal] mfG -- stefan -- -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201002/1 |
#18
|
|||
|
|||
Can I populate a control with the record number?
hi Tony,
On 22.02.2010 12:04, TonyWilliams via AccessMonster.com wrote: What have I done wrong? Should I add the tablhvdealspt1 in the query design screen? Mail me a sample... mfG -- stefan -- |
#19
|
|||
|
|||
Can I populate a control with the record number?
Was this a sample of the database or the data that's going into it? Also how
do I email it to you? Thanks Tony Stefan Hoffmann wrote: hi Tony, What have I done wrong? Should I add the tablhvdealspt1 in the query design screen? Mail me a sample... mfG -- stefan -- -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201002/1 |
#20
|
|||
|
|||
Can I populate a control with the record number?
hi Tony,
On 24.02.2010 12:10, TonyWilliams via AccessMonster.com wrote: Was this a sample of the database or the data that's going into it? Also how do I email it to you? ahh, I see you're not using NNTP... can you publish it on some free space in the web? mfG -- stefan -- |
Thread Tools | |
Display Modes | |
|
|