Modules

[freddie1437]

Hello, I have a question on Modules. I have made some modules, but how do I apply those modules to my forms or queries?

Thank you

[missinglinq]

If you're talking about a standard module, there is no "applying" it to a form or report. You can call any of the functions that reside inside your modules from code in forms or reports. Be aware that the module's name must not be the same as the name of any functions that it contains.

Linq ;0)>

[freddie1437]

If you're talking about a standard module, there is no "applying" it to a form or report. You can call any of the functions that reside inside your modules from code in forms or reports. Be aware that the module's name must not be the same as the name of any functions that it contains.

Linq ;0)>

Well I received a module converting an AS400 Date to regular date. The format of the date on AS400 is 1080904 would be today's date. I received the module converting that to 09/04/08, but it wont work. I wanted to know how to make that apply to my form.

[missinglinq]

You need to post the function within the module that's used to convert the date.

Linq ;0)>

[NeoPa]

Any procedures in your modules must be declared as Public if they are to be called from outside of the same module.

If it's no more than 50 lines, consider posting the whole module and we can see what you're working with.

[freddie1437]

Option Compare Database
Option Explicit
 
Function DateConvert(InDate As Variant) As Date
'the purpose of this function is to convert the ADR7 date format into a regular date format
 
Dim NewDate As Date
 
If InDate = 0 Then GoTo EndLine
 
If InDate <> "" Then
    If (Left(InDate, 1) = 1) Then
        NewDate = Mid(InDate, 4, 2) & "/" & Mid(InDate, 6, 2) & "/20" & Mid(InDate, 2, 2)
    Else
        NewDate = Mid(InDate, 3, 2) & "/" & Mid(InDate, 5, 2) & "/19" & Mid(InDate, 1, 2)
    End If
Else
    NewDate = "01/01/1900"
End If
 
DateConvert = NewDate
 
EndLine:
 
End Function

Thank you

[NeoPa]

If you change line #4 from

Function DateConvert(InDate As Variant) As Date

to

Public Function DateConvert(InDate As Variant) As Date

it should become visible to the rest of your project.

Good luck and welcome to Bytes! :)

[freddie1437]

I changed that, but do I have to name my dates as the date on the module?

[Stewart Ross]

No! Like all procedures and functions, the variables used internally within the procedure or function are local to that procedure and have nothing whatsoever to do with other fields etc. You call the function by supplying a fieldname or value of the appropriate type as an argument to the function in its heading like this:

NewDate: DateConvert([yourAS400datefield])

or using the SQL equivalent

SELECT DateConvert([yourAS400datefield]) AS NewDate FROM YourTable ...

-Stewart

[freddie1437]

Where do I insert this line? I am not very familiar with Modules or Codes. Any help would be greatly appreciated.

[NeoPa]

Well, where do you want to convert the format of your dates?

This is surely your starting point. We can't tell you that. You haven't even told us that yet.

[freddie1437]

I want to convert them on my forms and queries if possible.

[NeoPa]

Freddie, your minimalist approach to answering questions is not going to help us to help you.

From the little you have said I can only say that these general places are where you want to call the function from.

[freddie1437]

My bad, I building a database through my as400 ODBC connection and the dates are in wrong format and I want to allow them to be in normal format which is why I inserted the Module, but it doesn't change on my forms and since I haven't used Modules much, I don't know where or how to make these modules work.

[NeoPa]

Do you have an example of where you need an AS400 date to work as a converted one. Tell us what (and how) what you currently have refers to the date and we will see if we can provide a version which uses the converted date.

This could include the existing SQL for a query for instance, or some existing code, or even a TextBox on a form (this would not be updatable if the data is passed through the function of course).

[freddie1437]

Well I am using the date in a form. I have the date in a combo box in a query so that they choose the date on the combo box and it will report the data. They will not be able to modify or add any dates because they are linked to the AS400, but presenting them is where I would like them to be regular date.

[Stewart Ross]

Hi freddie. If your combo's row source is a query then you need to alter the query to use a call to the function instead of a direct reference to the AS400 date field. The syntax of that call has already been posted in post # 9.

If you are linking your combo to the AS400 table directly you will need to change this to an Access query based on that table, using the same syntax as shown in post 9 for the computed field.

Neither of these steps is difficult to do - it should take less than a minute. I do feel that you must have very limited Access experience, however, because your answers show only limited understanding of what a function is, how code modules contain procedures and functions, and the calling conventions for their use in queries and so on. I can only suggest that you look up good introductory texts on Access, and review the sample Northwind database which comes with Access for pointers on how a small amount of VBA coding can be used to link forms together in useful ways for users.

-Stewart

[freddie1437]

Well I don't have much experience with Access, barely getting my feet wet. I have used Access, just not too much coding which is what I really want to get into. Do you have any boxes or websites you would recommend me to learn more about code?

[Stewart Ross]

Hi freddie. There are some usful links in post 2 of this thread which may help you.

-Stewart

[freddie1437]

Great, Thank you! I appreciate the help!