Ms access

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Attachments

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.
 

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
 

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)
 

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.
 

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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
 

Attachments

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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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.:)
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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.
 

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top