setting a parameter as a default value 1

[ironj32]

I have a parameter query:

SELECT [Account Information].AccountNbr, [Account Information].VendorID
FROM [Account Information]
WHERE ((([Account Information].AccountNbr) Like '*-' & (1+Val(Mid([Forms]![frmAcctSearch]![frmVendorIDAcctQrySub].[Form]![AccountNbr],InStrRev([Forms]![frmAcctSearch]![frmVendorIDAcctQrySub].[Form]![AccountNbr],'-')+1)))));

If the above query does not return any results then I would like to set the calculated parameter from above as the default value for [frmAccountInfoUpdate].AccountNbr


Thanks for your input!

 

[Remou]

Have you considered DlookUp?

strNumber=1+Val(Mid([Forms]![frmAcctSearch]![frmVendorIDAcctQrySub].[Form]![AccountNbr],InStrRev([Forms]![frmAcctSearch]![frmVendorIDAcctQrySub].[Form]![AccountNbr],'-')+1))

If IsNull(DlookUp("AccountNbr", "Account Information","AccountNbr) Like '*-'" & strNumber & "'") Then
Me.[frmVendorIDAcctQrySub].[Form]![AccountNbr]=strNumber
End If

Or such like.

 

[TheAceMan1]

[blue]If the above query does not return any results then [purple]I would like to set the calculated parameter from above as the default value for [frmAccountInfoUpdate].AccountNbr[/purple][/blue]

[BLUE]If it fails . . . why set anything to a failure! . . .[/blue]

[blue]Your Thoughts . . .[/blue]

See Ya! . . . . . .

 

[Remou]

Aceman

Surely ironj32 is saying "if the look-up fails"? The number is clearly calculated (+1) and therefore the look-up is a valid check to see if a number exists before assiging it.

I would be glad of ironj32 comment on this.

 

[ironj32]

Remou has it right. I want to make sure that there is not an account already set up with that number in it.

 

[ironj32]

This is kind of off the topic. This database that I am working with is already getting pretty large...there is one table that over 16,000 records in it, and another that has about 5,000. The query's that we use are starting to take a little while to run. I believe that it was created only about a year ago and it is only going to continually get bigger each day. Do you think it would be bennificial for us to switch to something like MS SQL before this db get's really big? I just recently gratuated from school last spring so I am pretty new to all of this. Your thought are greatly appreciated.

 

[Remou]

16,000 records is not at all large for an Access database. I have run queries on tables with 750,000 records and other posts have mentioned larger databases. However, it is not the number of records that counts, but the size of the database:
2 gigabytes. However, because your database can include linked tables in other files, its total size is limited only by available storage capacity.
- Microsoft

Here are some notes on speed:
Do you regularly compact and repair your database?
Make sure that you have turned off Track Name Auto-correct (Tools->Options). It is an anti-feature.
Check that your queries are optimized. If you have problems with the speed of a particular query, post the SQL in the Microsoft Jet forum, some of the people there are very sharp indeed.

 

[ironj32]

Thanks!
I will have to do some research. I started this job just in August and have only recently began to work with this db.
What does the Track Name Auto-correct do?

 

[Remou]

Ruin your database! Seriously:

[tt]In Microsoft Access 97 and previous versions, if you rename a field in a table, the objects based on that field no longer display the data from that field. In Access 2000 or later, you can make Access track name changes, or automatically correct common side effects that occur when you rename forms, reports, tables, queries, fields, text boxes or other controls in a Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.). This feature is called Name AutoCorrect (Name AutoCorrect: A feature that automatically corrects common side effects that occur when you rename forms, reports, tables, queries, fields, or controls on forms and reports. However, Name AutoCorrect cannot repair all references to renamed objects.).[/tt]

-

http://office.microsoft.com/en-us/access/HP052575231033.aspx