Solved

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

Posted on 2004-04-21
12
304 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 500 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 50

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 50

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 50

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

777 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