Posts by boblarson

Okay, the first thing is that you should not have Table 2 - the Invoice Totals. That stores calculated data which is not a properly normalized structure. So, you would build that by pulling the Invoice Details and summing them.

From your post here's how I see the table structure:

(Client info)Table 1
PK - ClientID
CompanyName
ContactFirstName
ContactLastName
Address
City
State
Zip
Phone Number
Fax Number
E-mail

If the client can have more than one contact, you might want to revise that first table to be like this:

(ClientInfo) Table 1
PK – ClientID
CompanyName
CompanyAddress
CompanyCity
CompanyState
CompanyZip
CompanyPhone
CompanyFax
CompanyWebsite

(Contacts) Table 1a
PK - ContactID (Autonumber)
ClientID – Long Integer (FK)
ContactFirstName
ContactLastName
Address
City
State
Zip
Phone Number
Fax Number
E-mail
UseCompanyAddress (Yes/No)


(Client invoice Header)Table 2
PK-ClientInvoiceID
ClientId
InvoiceDate
StudyNameID

(Invoice Details) Table 3
PK – InvoiceDetailsID (Autonumber)
ClientInvoiceID – Long Integer (FK)
Quantity
SupplyID – Long Integer (FK)

(Supplies) Table 4
PK -- SupplyID (Autonumber)
SupplyDescription


(Payment itemization)Table 5
PK – PaymentID (Autonumber)
ClientInvoiceID – Long Integer (FK)
CPI
Payment

What is PRI invoice number???  I don’t think you need this table
(PRI invoice number) Table x
PK - PRI invoice ID
Client id
Client invoice id

(Studies) Table 6
PK – StudyID (Autonumber)
StudyName
ClientID – Long Integer (FK)
PONumber
Jobnumber
MarketID – Long Integer (FK)
StudyDate

(Study Type) Table 7
PK- StudyTypeID
StudyType

(Market) Table 8
PK - MarketID – (Autonumber)
MarketName

I don't know what CPI standa for …

In combobox use onchange() event

Not the On Change event. AFTER UPDATE event.

.. and pass this value to a function as argument and use ajax to dynamically update second combo box...
Study little bit about ajax.

What are you talking about Ajax here for? This is in an Access database and Access databases do not use Ajax. I think you misunderstood panhwer which forum category you were in.

Use the "On Change" event in your first combo box, update the "filter" property on your second combo box and issue a refresh.

Actually it should be in the combo's AFTER UPDATE event, not the On Change. On Change will also fire for every keystroke if someone starts typing in it which can cause problems and slow things down, depending on things.

Hi

I want to figure out a way to update multiple rows that depend on a couple of variables. I don't really know the syntax in SQL, but I know what I would do in C, so maybe if you could translate this...

switch (x)
     case 0:
           switch(y)
             case 0: myVar = var;
             case 1: myVar = someOtherVar;
     case 1:

.
.
.

Something like that is what I need to do. I could do it all with a bunch of queries but I don't really want to do it that way.

In Access it would be something like

Select Case x
    Case 0
        Select Case y
          Case 0
             MyVar = var
          Case 1
             MyVar = SomeOtherVar
        End Select

    Case 1
         Select Case y
             Case 0
                MyVar = OneMoreVar
             Case 1
                MyVar = GotVar
         End Select

    Case Else
          Msgbox "This has not been configured."
   
End Select

Ok, so basically all 6 tables in my database should have a primary key to foreign key relationship to each other?

I would say that, NORMALLY you would have tables which are linked together in some way. It is RARE to not be able to link them together. But also, if you do have them linked, if the links seem to form a big circle of sorts then you probably have something wrong with the design.

So, if you would like to list out the tables you have and their fields I'm sure that we could give you more detailed assistance, instead of the generalizing which may, or may not, fit your situation.

Yes, it is in MSAccess, it's the first window you see when you open up an MDB file. It shows your list of tables, or forms, or queries, etc. It is referred to as the "Database" window in the documentation.

It is also referring to the NAVIGATION PANE which is what the OP has referred to. So, they would have to

If Access 2007
Go to the ROUND OFFICE BUTTON > ACCESS OPTIONS > CURRENT DATABASE and then they can set the startup form and also UNCHECK the SHOW NAVIGATION PANE checkbox. However they will also need to uncheck the USE ACCESS SPECIAL KEYS or else the user can simply press F11 to open the navigation pane (or database window if in an older version).

If Access 2010
Go to FILE > ACCESS OPTIONS > CURRENT DATABASE and do the same as the Access 2007 instructions.

Hi,

I am trying to come up wit a database design for a small school. it works this way: in AC-Year, there are 3 terms with each term having 2 semesters. New students get registered at the start of each term. A student may take entrance exam. if he fails, he starts at freshman then after the semester, he can select department and program (i.e. diploma, certificate...). A student can select certificate accounting, for e.g., but if he is good, he can continue to diploma accounting program, taking the courses he needs only.

how do i store that information? How do i keep track of where he is studying? If he is upgraded, how do i keep his past? Please some tip as i am totally lost on this one.

Go here

http://www.databaseanswers.org/data_models/

and look down the page for "STUDENTS" and there are a lot of data models there to choose from. You can look to see if any might work for you.

You don't need to use the search to find the record in the other table. You would use a SQL Statement and recordset to bring back the appropriate record if there was one:

'SEARCH FOR THE RECORD IN REMARK TABLE
   Dim strSQL As String
   Dim strWHERE As String
   Dim rs As DAO.Recordset
   
   ' I assume that BuildCriteria does NOT return the word WHERE at the beginning
   strWHERE = BuildCriteria("[IDOp]", dbInteger, OpNameClick)

   strSQL = "SELECT * FROM Remark WHERE " & strWHERE

   Set rs = CurrentDb.OpenRecordset(strSQL)
   If rs.RecordCount = 0 Then
     Msgbox "Record does not exist"
   Else
     ' do whatever here with the record you have found from the other table
     ' not sure what it is you really want or what to do with it.
   End If

   rs.Close
   Set rs = Nothing

Below is the connection string,

<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="true"
' Catalog=""
' Schema=""
Dim MM_cnxxxx_STRING
MM_cnxxxx_STRING = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("./uuuu.mdb")
%>

Check out the Connection String for Access here:
http://www.connectionstrings.com/access

Yours doesn't look right.

hi all,
i have created a crosstab query and one of the row heading contains a hyperlink field.
my problem is that the field is not working,
and on top of that it gives corrupted values too.

pls help
thanks

I don't believe you can use that directly in a crosstab. Create your crosstab query without that field and then create a select query which joins the crosstab with the hyperlink field in it.

Ok, so after hours and hours of testing and searching online I'm almost convinced that this can't be done, but I'm asking here for help maybe someone can give me any idea as to why this isn't working.

I'm doing this query:

SELECT * FROM(
(SELECT t1.PAG5 from table1 t1 where t1.PAG5 > 0) 
UNION ALL
(select t2.PAG1 from table1 t2 where t2.PAG1 > 0)
)

The bad part: it has to be access 2.0

This gives me an error after the first FROM. I'm guessing access 2.0 doesn't allow nested Select with Union. Is this true?

PS: Running the two Union queries by themselves works.

I just tried with this and it works:

Select * FROM (SELECT t1.PAG5 from table1 [B]As[/B] t1 where t1.PAG5 > 0
UNION ALL
select t2.PAG1 from table1 [B]As[/B] t2 where t2.PAG1 > 0)

I have done this query : Balance:[Medical]![Price]-750

That isn't a query. That may be PART of a query from the QBE grid but it is definitely not the entire query. If it is then that would explain a lot.

Post the SQL of the query (Go to VIEW > SQL VIEW to be able to get that and copy and paste it here.

I forgot to include how to call this.

It should be:

Call AdjustProcNum("YourTableNameInQuotes", "YourFieldNameInQuotes", YourNewNumber)

Copy this function into a standard module (not form, report or class) and name the module something other than the name of the function:

Function AdjustProcNum(strTableName As String, strFieldName As String, lngNewNum As Long)
   Dim strSQL As String
   Dim rst As DAO.Recordset

strSQL = "Select [" & strFieldName & "] From [" & strTableName & "] " & _
         "ORDER BY [" & strFieldName & "]"

Set rst = CurrentDb.OpenRecordset(strSQL)

Do Until rst.EOF
   If rst(strFieldName).Value >= lngNewNum Then
      rst.Edit
      rst(strFieldName).Value = rst(strFieldName).Value + 1
      rst.Update
   End If
   rst.MoveNext
Loop

MsgBox "You can now enter the new process number " & lngNewNum, vbInformation

rst.Close
Set rst = Nothing   
End Function

I'm assuming Outcome is numeric but it wouldn't have to be but you would change the value for NZ to vbNullString if it is text.

MyNum1 = ]")[B]Nz([/B]DLookup("[Outcome]", "Remark", _
      "[IDRemark] = Form![SelectTxt]")[B],0)[/B]
OutTxt.Value = MyNum1

Set up the table to be imported to first with the right datatypes and all and then import the data.

You can try setting the Search button's DEFAULT property to YES and see if that helps.

You are using the wrong ODBC driver. You should be using one for ACCESS. You don't select the driver based on what you are programming FROM but where you want to connect TO.

So you want the Microsoft Access ODBC driver and the connection strings are available here:

For Access 2003 or prior

for Access 2007 or greater

It is not a simple task for which you seek. About the only way you will get to do it, short of being an EXPERT Access Programmer and writing a LOT of code is to purchase Shrinker/Stretcher from Peter's Software which does do this for you.

1. Is this a saved query (not being done in code)?

2. Is this combo box on a subform perhaps?

3. As for concatenating, it has to do with the way the value is inserted and not the form reference so the query doesn't have to do an evaluation of the control.

4. Please post the ENTIRE SQL string, not just what YOU think is relevant.

What is the SQL of the query. I'll bet you included the combo inside the SQL and didn't concatenate it in so that it would pass the VALUE and not have to try to evaluate it.

Hi I am new to VB.NET but for my final year project have started a system for childrens daycare center. I want to create user accounts which I think I should be able to do in acess.

How do I link this to VB.NET? is it hard? what will vb.net let me do?

If you are using VB.NET as the frontend, then don't go trying to create user accounts in Access. You can use tables in Access to store your user account information but use VB.NET to handle the users and the access to various points. Access security (available only with mdb files and not the newer Access 2007/2010 ACCDB files) is meant for use when you use Access directly as a frontend.

i m facing problem while conencting with sql server mangemnet studio

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

You posted this in the wrong category. It should be in the SQL Server category instead.

You don't link SharePoint to an Access database, you link an Access database to SharePoint. Access can display SharePoint data and use it for creating reports, not the other way around. You may want to read this for more information:

http://www.databasejournal.com/features/msaccess/article.php/3809741/SharePoint-and-Microsoft-Access---Getting-Motivated.htm

Why not just create a frontend in Access?

I answered your question in your other post here:

http://www.daniweb.com/forums/thread331196.html

With tab controls you do not reference any tabs. Just reference a control as if it was on the form itself. If you are referencing a control on a subform within a tab just use the normal syntax for referring to a control on the subform. There is no special referencing needed. Pretend the tabs don't even exist.

Control Source of the text box:

=DMax("YourDateFieldNameHere", "YourTableNameHere")

Replace YourDateFieldNameHere with your actual field which has the dates and YourTableNameHere with the name of the table where the date field resides. And KEEP THE DOUBLE QUOTES.

No, each computer has to have it set up. You can run a batch file to update registry settings (see here for more about the registry settings required).

Yes, you should make an ACCDE file. What is happening right now is that you have compile errors that you need to fix before it can compile to an ACCDE. So, open the VBA window, and go up to DEBUG > COMPILE {yourprojectnamehere} and run the compile. Fix the error that it highlights and then go compile again and keep fixing the errors until you have no more errors. Then you should be able to create the ACCDE file.

Use a REPORT for printing. Forms are not designed for printing.

Im having trouble with a running sum for my VAT analysis section of a report.

Whats happening is the vat is calculated and rounded appropriately. But the running sum seems to be calculated on the pre rounded figure, is there anyway to rectify this.

So i have following figures
245.70 at 21% which is 51.597 when rounded goes to 51.60
120 at 21.5% which is 25.80 with no need to round
382.20 at 13.5 which is 51.597 when rounded goes to 51.60

i want it to add the rounded figures so so 51.60 + 25.80 + 51.60 =129.00. So it looks OK on the report

Instead it is displaying 128.99 which is rounded from 128.994 since (51.597 + 25.80 + 51.597) = 128.994

Any help on the issue is much appreciated.

Try using the ROUND function on the control source of the text box that you are using for the running sum.

Or use the Round function in the query itself.

Compare field Field1 with Ucase(Field1) return 0 ever.. but I have any register with Field1 in mix case. Why Access retur every igual ?

Access is not case sensitive. If you want to compare strings you need to use binary.

Look up the strComp function.

You could download and install the 2007 runtime but it would only let you USE the database, not dig into the design views. As mentioned by Gm Xtrm, you would need to have them save it into 2003 format (if they haven't used any of the features of 2007 which will preclude them from saving to 2003 format). Otherwise the other way is for you to purchase a copy of 2007 (or download a trial of Office 2007 from Microsoft's website).

So you actually created an import specification which is called "File Import Specification?"

Perhaps if you haven't figured it out yet and want to send me your database file and your csv file, I can see what is up.

I can PM you my email address if you wish. Let me know.

I think this may be what you're looking for.

I would also say that you need to

1. Be using split databases

2. Have each user using a copy of the frontend on their machine.

This is the way all multi-user databases should be done anyway, otherwise you're playing Russian Roulette with corruption.

What has been showing up have been questions from people who are using the same database file and having users on different versions running the same file at the same time. If that occurs, you run a big risk of the users not being able to use the file.

Tab controls are interesting in that you rarely need to do anything at all with them or reference them. In your case you need to set focus to tab 5 and then refer to the subform on subform. You have to set focus first. I will show you a way to refer to forms and controls which most don't use but with nested subforms it works well.

First the set focus

Forms!frmMain.ifrmTABset.Pages(4).SetFocus

Since the pages are zero based, page 5 would be 4 in the scheme. You can also use just the page name instead of the tab control name. So if your page was actually named page5 then you would be able to go with

Forms!frmMain.page5.SetFocus

And then you need to refer to the subform item

Forms("frmMain").Controls("sfrmMain").Form.Controls("sfrmUser").SourceObject = "sfmSETcfg"

I think you may want to read my tutorial on subforms. It helps with understanding when to use the .Form. and when not to.
You can find it here:
Easy Way to Remember Subform Syntax

Perhaps you would like to use Microsoft's free Visual Web Developer. It is the free, limited edition of ASP.NET.

http://www.microsoft.com/downloads/details.aspx?FamilyID=f3fbb04e-92c2-4701-b4ba-92e26e408569&DisplayLang=en

please if you have any version is there any way it can get to me please..

That doesn't answer my question (but it is likely what you have isn't compatible with Vista although we won't know unless we can get the version).

Plus, it isn't the way it works. You have to purchase the product. It isn't something that is freely shareable.

i think that it should be Buisness edition

There is no "business edition" of Visual Studio. There is Standard, Professional, and Enterprise.

But I was asking the VERSION LEVEL: 2002, 2003, 2005, or 2008 If .NET

If it is another type of Visual Studio (VS 6, 5, 4, etc.) those are older versions.

thank you boblarson i really want to learn it, but i have a little problem the visual studio i have do no seem to be runing on my windows vista software, what do u think tha i should do.

Which version do you have?

i eqally have the same problem and i need urgent help can some body give us a detailed explanation on how to go about it.

That's not a "quick" thing to be able to do. You need to go learn how to use a programming language if you want to do this and do it so that it is secure.

See here for free tutorials:
FunctionX ASP.NET Tutorial

because its my assignment and i don't know how to do that :(

Sounds like the assignment may have been misunderstood. But since we don't have the assignment (nor access to the instructor) we can't verify that.

If what you are saying is completely true, the way that it was said originally, then I would love to see what the instructor gives as the answer because I've never seen that done.

You are missing a .Form between the subform (actually, you need to make sure you are referring to the actual subform CONTAINER control that houses the subform on the main form) and combo and since it is on a nested subform, it is easier to use:

Me.Parent.Controls(sbfZone4Areas).Form.Controls(ssubZone4).Form.Controls(cboDetail).RecordSource = "qryDetailZ4MainSt"

The goal is to have the user make a choice in the first combo box. That choice will dictate which one of the 4 look-up tables or, preferably, queries on those tables becomes the Row Source for the second combo box. I suspect that I'm not using proper addressing in the After Update If statement on the 1st combo box.

So what is your current code in the After Update event of the first combo?

What do you mean by you want the other combo box to have a selected choice? Do you mean that you want one value to be available in the second combo based on the first selection, or SEVERAL possible values in the second combo based on the selection of the first?

Check out my sample here:
http://downloads.btabdevelopment.com/Samples/dates/ReportAndForm.zip

hi all!
I need to make a form in Ms-Access as EXE file. So that the file can be runned without opening Ms-Access. I know that, it is possible in Vbasic but whether it is possible to do in Ms-Access.
Thanks

The short answer is NO, you cannot do this the way you are wanting. You've been given what IS possible by kb.net. You CANNOT, under any circumstances, make an EXE file out of an Access database or form.

If you need an exe file, you need to create it in a programming language such as VB6, VB.NET, VC#, VC++, JAVA, etc.

Not sure what you are actually doing. Are you wanting to export the data from Access into SQL Server 2005 Express?

If so, you should be able to use the (on the EXTERNAL DATA Tab) EXPORT > MORE > ODBC DATABASE to send the tables to SQL Server.