Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How can I get a value from a Form by coding??

Posted on 2004-04-21
12
Medium Priority
?
340 Views
Last Modified: 2006-11-17
Here is part of the coding:

Private Sub Edit_Click()

stDocName = "DataEntry_Staff_Edit"

stStaffCode = (How can I get a value from form?? let's say I would like to get the staff code from a text box "StaffCode" from a form "DataEntry_Staff")

DoCmd.OpenForm stDocName, acFormEdit, acWindowNormal

End Sub
0
Comment
Question by:ryan0107
  • 5
  • 4
  • 3
12 Comments
 
LVL 6

Accepted Solution

by:
nathaniel earned 2000 total points
ID: 10885728
In Access2000 try these:

Private Sub Edit_Click()

  stDocName = "DataEntry_Staff_Edit"

  stStaffCode = [DataEntry_Staff]!StaffCode

  DoCmd.OpenForm stDocName, acFormEdit, acWindowNormal

End Sub
0
 

Author Comment

by:ryan0107
ID: 10885754
Sorry, then how can I use   DoCmd.OpenForm to open the stDocName and with filtering the stStaffCode??
0
 
LVL 54

Expert Comment

by:Ryan Chong
ID: 10885756
Try this:

in a module add this:

Public Function ifIsLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
        If Forms(strFormName).CurrentView <> 0 Then
            ifIsLoaded = True
        End If
    End If
End Function

then to check the value, use this:

Private Sub Command1_Click()
    If ifIsLoaded("DataEntry_Staff_Edit") = 0 Then
        MsgBox "DataEntry_Staff_Edit Not loaded!"
    Else
        MsgBox Form_DataEntry_Staff_Edit.Controls("StaffCode").Value
    End If
End Sub
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 54

Expert Comment

by:Ryan Chong
ID: 10885763
DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][, windowmode][, openargs]

Example:

DoCmd.OpenForm "DataEntry_Staff_Edit", , ,"stStaffCode= '" & Form_DataEntry_Staff_Edit.Controls("StaffCode").Value & "'"
0
 
LVL 6

Expert Comment

by:nathaniel
ID: 10885814
to apply it as filter in opening form, you may try this:

CHANGE
DoCmd.OpenForm stDocName, acFormEdit, acWindowNormal

TO:

'if stStaffCode is of string type:
DoCmd.OpenForm stDocName,,,"stStaffCode=" & """" & stStaffCode & """", acFormEdit, acWindowNormal


'if stStaffCode is of number type:
DoCmd.OpenForm stDocName,,,"stStaffCode=" & stStaffCode, acFormEdit, acWindowNormal

hope this helps,
0
 

Author Comment

by:ryan0107
ID: 10885826
Sorry,  when I use this code:

DoCmd.OpenForm "DataEntry_Staff_Edit", , ,"stStaffCode= '" & Form_DataEntry_Staff_Edit.Controls("StaffCode").Value & "'"

An error message was displayed as follow:

"Run Time Error 3075
Syntax Error (missing operator) in query expression 'stStaffCode=1234'"

Can you help me to solve it??
0
 
LVL 54

Expert Comment

by:Ryan Chong
ID: 10885900
>>"Run Time Error 3075
Syntax Error (missing operator) in query expression 'stStaffCode=1234'"

is you missing something? >>'stStaffCode=1234'", this definitely is wrong! try:

"stStaffCode= '" & Form_DataEntry_Staff_Edit.Controls("StaffCode").Value & "'"

stStaffCode='1234'"

or

"stStaffCode= " & Form_DataEntry_Staff_Edit.Controls("StaffCode").Value

stStaffCode=1234"
0
 

Author Comment

by:ryan0107
ID: 10885951
Basically,  here is the code:

Private Sub Edit_Click()
stDocName = "DataEntry_Staff_Edit"
stStaffCode = [Forms]![DataEntry_Staff]![Staff Code]
DoCmd.OpenForm stDocName, , , "Staff Code= " & stStaffCode, acFormEdit

End Sub

An error message shown as :

>>Run Time Error 3075
Syntax Error (missing operator) in query expression 'Staff Code=1234'

Any idea can give me??
0
 
LVL 6

Expert Comment

by:nathaniel
ID: 10886042
We'll start with this first:

is the stStaffCode a number type of string type?
0
 
LVL 6

Expert Comment

by:nathaniel
ID: 10886098
We'll start with this first:

is the stStaffCode a number type or string type?

if string type, use:

DoCmd.OpenForm stDocName, , , "Staff Code= " & """" & stStaffCode & """", acFormEdit

if not, use:

DoCmd.OpenForm stDocName, , , "Staff Code= " & stStaffCode, acFormEdit

0
 

Author Comment

by:ryan0107
ID: 10887194
Thanks.  I can open the form successfully... but can't filter just that staffcode record from my table.  All records were extracted from a table just as usual.

Basically,  I want to use above codes to open a new form with just one related profile (making use of Staff Code) and then go right ahead for edit.

Do you think my coding is right??

Sorry I have too many questions coz I'm just new to Access
0
 
LVL 6

Expert Comment

by:nathaniel
ID: 10887402
That's ok ryan don't give up!

If you can open the form successfully, then we now need to fix the condition part.

try this one first:

msgbox stStaffCode
DoCmd.OpenForm stDocName, , , "[Staff Code] = " & stStaffCode, acFormEdit

explanation:
the msgbox.. will let you know if you are getting a value

let me know if it works,



0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question