1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Ms access

Discussion in 'Business Applications' started by fahad_m85, Jan 1, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. fahad_m85

    fahad_m85 Thread Starter

    Joined:
    Apr 3, 2009
    Messages:
    129
    hello OBP
    hope you are fine and recognize me

    its about access that i was learning from you, i stopped working due to some problems in my company but now i started it again and now i quit near to create my database but there are some problems, i upload the database (ms access 2003 format) please find the solutions

    problem 1. when i record a new raw material by a form "add new raw material item" and when select existing vendor from "vendor" field it saves the record but in vendor name it puts the vendor id instead of vendor name in "raw material" table, is this a relationship problem ?

    problem 2. when i make a new invoice and save it, it recorded but i also want to print the invoice that i currently made with a specific format by pressing a print button on "invoice" form how could i do this i also upload the format of invoice which i want to print. and it also having the same problem like problem no. 1 in "invoice to" field in invoice table where i want to save the party name from "parties" table.

    waiting for your soon reply
     

    Attached Files:

  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Yes, I remember.
    First of all, problem 1 is not a problem at all. That is how it is supposed to work. It stores the ID in the table, but displays the Vendor Name when you want it to by using a Combo on a Form, or a Query for a Report.
    I will take a look at your Invoice.
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Shouldn't your company name be on the Invoice somewhere?
     
  4. fahad_m85

    fahad_m85 Thread Starter

    Joined:
    Apr 3, 2009
    Messages:
    129
    yes, on the top of the page

    but please also teach me that how to do this
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    This part is very very difficult, unless you want to enter it manually at the time of printing.
    In Words: (IN WORDS)
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You need to have a look at the database that I posted in your last Thread to see how the Tables should be related & how Subforms work.
     
  7. fahad_m85

    fahad_m85 Thread Starter

    Joined:
    Apr 3, 2009
    Messages:
    129
    in words mean the net payable amount written in words

    is this not possible with to make a form of this format and then linked it with invoice form by print a form button?
    i tried it but it prints all records of invoice table
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Hi, I found this searching with googleon the Orgid site

    Code:
    Option Explicit
    
    Function ConvertCurrencyToEnglish(ByVal MyNumber)
    Dim Temp
             Dim Dollars, Cents
             Dim DecimalPlace, Count
             ReDim Place(9) As String
             Place(2) = " Thousand "
             Place(3) = " Million "
             Place(4) = " Billion "
             Place(5) = " Trillion "
    
             ' Convert MyNumber to a string, trimming extra spaces.
             MyNumber = Trim(Str(MyNumber))
    
             ' Find decimal place.
             DecimalPlace = InStr(MyNumber, ".")
    
             ' If we find decimal place...
             If DecimalPlace > 0 Then
                ' Convert cents
                Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
                Cents = ConvertTens(Temp)
                ' Strip off cents from remainder to convert.
                MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
             End If
             Count = 1
             Do While MyNumber <> ""
                ' Convert last 3 digits of MyNumber to English dollars.
                Temp = ConvertHundreds(Right(MyNumber, 3))
                If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
                If Len(MyNumber) > 3 Then
                   ' Remove last 3 converted digits from MyNumber.
                   MyNumber = Left(MyNumber, Len(MyNumber) - 3)
                Else
                   MyNumber = ""
                End If
                Count = Count + 1
             Loop
    
             ' Clean up dollars.
             Select Case Dollars
                Case ""
                   Dollars = "No Dollars"
                Case "One"
                   Dollars = "One Dollar"
                Case Else
                   Dollars = Dollars & " Dollars"
             End Select
    
             ' Clean up cents.
             Select Case Cents
                Case ""
                   Cents = " And No Cents"
                Case "One"
                   Cents = " And One Cent"
                Case Else
                   Cents = " And " & Cents & " Cents"
             End Select
             ConvertCurrencyToEnglish = Dollars & Cents
    End Function
    
    Private Function ConvertHundreds(ByVal MyNumber)
    Dim Result As String
    
             ' Exit if there is nothing to convert.
             If Val(MyNumber) = 0 Then Exit Function
    
             ' Append leading zeros to number.
             MyNumber = Right("000" & MyNumber, 3)
    
    
             ' Do we have a hundreds place digit to convert?
             If Left(MyNumber, 1) <> "0" Then
                Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
             End If
    
             ' Do we have a tens place digit to convert?
             If Mid(MyNumber, 2, 1) <> "0" Then
                Result = Result & ConvertTens(Mid(MyNumber, 2))
             Else
                ' If not, then convert the ones place digit.
                Result = Result & ConvertDigit(Mid(MyNumber, 3))
             End If
             ConvertHundreds = Trim(Result)
    End Function
    
    Private Function ConvertTens(ByVal MyTens)
    Dim Result As String
    
             ' Is value between 10 and 19?
             If Val(Left(MyTens, 1)) = 1 Then
                Select Case Val(MyTens)
                   Case 10: Result = "Ten"
                   Case 11: Result = "Eleven"
                   Case 12: Result = "Twelve"
                   Case 13: Result = "Thirteen"
                   Case 14: Result = "Fourteen"
                   Case 15: Result = "Fifteen"
                   Case 16: Result = "Sixteen"
                   Case 17: Result = "Seventeen"
                   Case 18: Result = "Eighteen"
                   Case 19: Result = "Nineteen"
                   Case Else
                End Select
             Else
                ' .. otherwise it's between 20 and 99.
                Select Case Val(Left(MyTens, 1))
                   Case 2: Result = "Twenty "
                   Case 3: Result = "Thirty "
                   Case 4: Result = "Forty "
                   Case 5: Result = "Fifty "
                   Case 6: Result = "Sixty "
                   Case 7: Result = "Seventy "
                   Case 8: Result = "Eighty "
                   Case 9: Result = "Ninety "
                   Case Else
                End Select
    
                ' Convert ones place digit.
                Result = Result & ConvertDigit(Right(MyTens, 1))
             End If
             ConvertTens = Result
    End Function
    
    Private Function ConvertDigit(ByVal MyDigit)
    Select Case Val(MyDigit)
                Case 1: ConvertDigit = "One"
                Case 2: ConvertDigit = "Two"
                Case 3: ConvertDigit = "Three"
                Case 4: ConvertDigit = "Four"
                Case 5: ConvertDigit = "Five"
                Case 6: ConvertDigit = "Six"
                Case 7: ConvertDigit = "Seven"
                Case 8: ConvertDigit = "Eight"
                Case 9: ConvertDigit = "Nine"
                Case Else: ConvertDigit = ""
             End Select
    End Function
    
    
    
    If you put this in the VBA projects and use it will translate the numeric value to words

    See the excel attachmetn as an example
     

    Attached Files:

  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Hans, that is the code that I was talking about, I am not sure what Currency Fahad is working in.
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Well, I don't think it'll be that difficult for him to change the text from "Dollars" to "Baht" or any other currency.
    We'll wait and see.:)
     
  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    BTW, OBP, Is anybody looking at the Invoice part? The document to be printed?
    I don't mind taking a shot at it but it'll take some time since I've got a heavy schedule ahead at work.
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Yeah, I noticed. I'll dosome simple editting at least so that he can have something to work on.

    I think his currency is the PKR (Pakistanee Rupee).

    We'll just see how far we can get it to work, at least to put him on the right track.
     
  14. fahad_m85

    fahad_m85 Thread Starter

    Joined:
    Apr 3, 2009
    Messages:
    129
    yes my currency is in Pakistani rupee but this is not a serious problem i will fix it.
    actually the problem is that which i post first that is problem no. 1 if it is fixed then i am able to start working on database. second problem which is about "invoice" i think, i print it manually with excel until OBP find the solution but my first preference is to fix the problem no. 1 i tried it to fix, but failed not know why
    therefore, OBP please fix it and upload the db and then you start to find the solution of problem no. 2

    you told about your previous db you uploaded, OBP i made this new db with the help of ypur db but it is actually to complex, you have seen this new db it is too simple i want to start my work with this if i need more tables then i will add them later on but now i required only these
    hope you understand
     
  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Just to show the Curreny to words, I added the optional Currency possibility.

    I am not quite up-to-date with Problem 1. and / or Problem 2.
    As I see it now you expect the complete layout and design from one of the TGS members?

    I noticed that the db layout is really simple and you will need some queries and more to at least produce a simple invoice with order lines, unit values, totals by number of units etc.

    That's quite a task.
    Once the tables are correct the Invoice layout is simple but the basics need to be set first.
    I'll gladly help you out with the report part (Invoice) but then I'll need a correct working table structure
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/971943

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice