help setting string to equal table feild

[Herdrich]

Im trying to load a form set by strType that displays CACID that is equal to strCACID and if there is no match then open a new entry. I have no problem loading the new entry part by its self but when i try to add the equal to part in nothing works. Thank you for your help


strType is selected from a drop down box wich has the same name as the table and form being called.

strScan is a string from a barcode scanner which the strCACID is taken from.

If strType.[CACID] = strCACID Then
    strWhere = "(strType.[CACID] = strCACID)"
    DoCmd.OpenForm strType, acNormal, , strWhere, acFormEdit
       
Else
    DoCmd.OpenForm strType, acNormal, , , acFormAdd, acDialog, strScan
       
End If

 

[MajP]

strWhere = "strType.[CACID] = '" & strCACID & "'

 

[Herdrich]

I keep getting an compile error: invaild qualifier from the strType.[CACID] after the IF. Any ideas?

 

[MajP]

I am not sure what is what. You need a better naming convention.
forms are named frmSomeName
table are named tblSomeName
combos are named cmboSomeName
strings in code are named strSomeName

rename everything so it is logical and then repost.

 

[Herdrich]

If tblType.[CACID] = strCACID Then
    
   strWhere = "(tblType.[CACID] = strCACID)"
    
   DoCmd.OpenForm frmType, acNormal, , strWhere, acFormEdit
       
Else
    
   DoCmd.OpenForm frmType, acNormal, , , acFormAdd, acDialog, strScan
       
End If

 

[MajP]

I still do not understand what you are saying, your original post is really unclear.

Im trying to load a form set by strType that displays CACID that is equal to strCACID

????

Just a guess

If me.CACID = me.cmboSomeCombo Then

strWhere = "CACID = '" & me.cmboSomeCombo & "'"

DoCmd.OpenForm frmType, acNormal, , strWhere, acFormEdit

 

[Herdrich]

If tblType.[CACID] = strCACID Then

'Grabs info from record source table.cacid then checks to see if an etry is equal to the string strcacid

strWhere = "(tblType.[CACID] = strCACID)"

'If its the same then it sets the varriable strWhere to strcacid and opens form to edit that record

DoCmd.OpenForm frmType, acNormal, , strWhere, acFormEdit

Else
' if its not equal then it opens a the form to add a record
DoCmd.OpenForm frmType, acNormal, , , acFormAdd, acDialog, strScan

End If


Is that clear or should i reword it? Thank You

 

[dhookom]

I'm at least as lost as MajP.
If "tblType" is a table in your application and "CACID" is a field in that table, you can't just grab a value with:

  tblType.[CACID]

You might be able to use DLookup() or other domain aggregate function or possible if the code is running in a form, the form might have CACID as a control that could supply the value.


Duane
Hook'D on Access
MS Access MVP

 

[Herdrich]

Well this ended up working for me because if there was no match it just loaded an empty record anways. Thanks for the help.

If Not IsNull(Me.Type) Then
      frmType = Me.Type
End If


If Not IsNull(strCACID) Then
      strWhere = "([CACID] = '" & strCACID & "')"
End If



DoCmd.OpenForm frmType, acNormal, , strWhere, acFormEdit, acWindowNormal, strScan