Double Dlookup insert button

[cikwan82]

Hi every one, i already make this sql for dlookup table for may insert button, all going good, but when i'm going to make double command with different msg box for different criteria, it going fail

1. This my Working code:

If Me.txtidborang.Tag & "" = "" = (DLookup("NoGerankod", "HutangKeseluruhan", "NoGerankod='" & Me.txtnogeran & "'")) Then
MsgBox "Grant Number Invalid", vbOKOnly
Me.[cbostatuspembayaran] = "Geran Negatif"

CurrentDb.Execute "INSERT INTO Januari (bla..bla..bla..)

End if

2. This my not working code:

If Me.txtidborang.Tag & "" = "" = (DLookup("NoGerankod", "HutangKeseluruhan", "NoGerankod='" & Me.txtnogeran & "'")) Then
MsgBox "Grant Number Invalid", vbOKOnly
Me.[cbostatuspembayaran] = "Geran Negatif"

If Me.txtidborang.Tag & "" = "" = (DLookup("NoMatrikkod", "HutangKeseluruhan", "NoMatrikkod='" & Me.txtukmper & "'")) Then
MsgBox "This Student still have debt ", vbOKOnly
Me.[cbostatuspembayaran] = "Geran Aktif"

CurrentDb.Execute "INSERT INTO Januari (bla..bla..bla..)

End if
End if

Q: How can i combine two dlookup together for different msg box?

Thank you..

 

[cikwan82]

I also try this coding, stil not working.. please anyone help me..

If Me.txtidborang.Tag & "" = "" = (DLookup("NoGerankod", "HutangKeseluruhan", "NoGerankod='" & Me.txtnogeran & "'")) or (DLookup("NoMatrikkod", "HutangKeseluruhan", "NoMatrikkod='" & Me.txtukmper & "'")) Then
MsgBox "Grant Number Invalid" or "This Student still have debt ", vbOKOnly
Me.[cbostatuspembayaran] = "Geran Negatif" or Me.[cbostatuspembayaran] = "Geran Aktif"

CurrentDb.Execute "INSERT INTO Januari (bla..bla..bla..)

End if

 

[Galaxiom]

Your question can be solved with an ElseIf construct.

However can you explain this code which makes no sense to me:
If Me.txtidborang.Tag & "" = "" = (DLookup

Also a table with a month as its name would appear to be an incorrect data structure.

 

[cikwan82]

I'm new on this sql , hope u can guide me.. all my sql i just learn edit from website.. what do u mean Elself?

for : If Me.txtidborang.Tag & "" = "" = (DLookup

actually like this :

Private Sub cmdAdd_Click()
'when we click on button add there are two options
'1. for insert
'2. for update

If Me.txtidborang.Tag & "" = "" = (DLookup("NoGerankod", "HutangKeseluruhan", "NoGerankod='" & Me.txtnogeran & "'")) Then
MsgBox "Grant Number Invalid", vbOKOnly
Me.[cbostatuspembayaran] = "Geran Negatif"



If Me.txtidborang.Tag & "" = "" = (DLookup("NoMatrikkod", "HutangKeseluruhan", "NoMatrikkod='" & Me.txtukmper & "'")) Then
MsgBox "Student Ini Berhutang", vbOKOnly
Me.[cbostatuspembayaran] = "Geran Aktif"

'add data to table


CurrentDb.Execute "INSERT INTO Januari ( IDBorangkod, IDSampelkod, NoMatrikkod, NamaPenggunakod, NamaPenyeliakod, Kategorikod, ProsesPembayarankod, PusatPengajianUKMkod, Fakultikod, Statuskod, BilSampelkod, LabelSampelkod, JenisAnalisis1kod, Unit1kod, JenisAnalisis2kod, Unit2kod, JenisAnalisis3kod, Unit3kod, JenisAnalisis4kod, Unit4kod, JenisAnalisis5kod, " & _
" Unit5kod, JenisAnalisis6kod, Unit6kod, JenisAnalisis7kod, Unit7kod, JenisAnalisis8kod, Unit8kod, Masakod, KosAnalisiskod, NoGerankod, StatusPembayarankod, NamaInstitusiLuarUKMkod, AlamatLuarUKMkod, Masa1kod, Masa2kod, Masa3kod, Masa4kod, Masa5kod, Masa6kod, Masa7kod, Masa8kod, TextHarga1kod, TextHarga2kod, TextHarga3kod, TextHarga4kod, TextHarga5kod, TextHarga6kod, TextHarga7kod, TextHarga8kod, Catatankod, Total1kod, Total2kod, Total3kod, Total4kod, Total5kod, Total6kod, Total7kod, Total8kod, Totaltestkod ) " & _
" VALUES ('" & Me.txtidborang & "','" & Me.txtidsampel & "','" & Me.txtukmper & "','" & Me.txtnamapengguna & "','" & Me.txtpenyelia & "','" & Me.cbokategori & "','" & Me.cboprosespembayaran & "','" & Me.cbopusat & "','" & Me.cbofakulti & "','" & Me.cbostatus & "','" & Me.txtbilsampel & "','" & Me.txtlabel & "','" & Me.cboanalisis1 & "','" & Me.cbounit1 & "','" & Me.cboanalisis2 & "','" & Me.cbounit2 & "','" & Me.cboanalisis3 & "','" & Me.cbounit3 & "','" & Me.cboanalisis4 & "','" & Me.cbounit4 & "','" & Me.cboanalisis5 & "','" & Me.cbounit5 & "','" & Me.cboanalisis6 & "','" & Me.cbounit6 & "','" & Me.cboanalisis7 & "','" & Me.cbounit7 & "','" & Me.cboanalisis8 & "','" & Me.cbounit8 & "','" & Me.txtmasa & "','" & Me.txtkos & "','" & Me.txtnogeran & "','" & Me.cbostatuspembayaran & "','" & Me.txtnamainstitusi & "','" & Me.txtalamatluar & "'" & _
",'" & Me.Masa1 & "','" & Me.Masa2 & "','" & Me.Masa3 & "','" & Me.Masa4 & "','" & Me.Masa5 & "','" & Me.Masa6 & "','" & Me.Masa7 & "','" & Me.Masa8 & "','" & Me.TextHarga1 & "','" & Me.TextHarga2 & "','" & Me.TextHarga3 & "','" & Me.TextHarga4 & "','" & Me.TextHarga5 & "','" & Me.TextHarga6 & "','" & Me.TextHarga7 & "','" & Me.TextHarga8 & "','" & Me.txtcatatan & "','" & Me.Total1 & "','" & Me.Total2 & "','" & Me.Total3 & "','" & Me.Total4 & "','" & Me.Total5 & "','" & Me.Total6 & "','" & Me.Total7 & "','" & Me.Total8 & "','" & Me.Totaltest & "') ;"

cmdClear_Click




Else
'otherwhise (tag of txtidborang store the id of januari to modified)
CurrentDb.Execute "UPDATE Januari " & _
"SET IDBorangkod='" & Me.txtidborang & "'" & ",IDSampelkod='" & Me.txtidsampel & "'" & ",NoMatrikkod='" & Me.txtukmper & "'" & ",NamaPenggunakod='" & Me.txtnamapengguna & "'" & _
",NamaPenyeliakod='" & Me.txtpenyelia & "'" & ",Kategorikod='" & Me.cbokategori & "'" & ",ProsesPembayarankod='" & Me.cboprosespembayaran & "'" & ",PusatPengajianUKMkod='" & Me.cbopusat & "'" & _
",Fakultikod='" & Me.cbofakulti & "'" & ",Statuskod='" & Me.cbostatus & "'" & ",BilSampelkod='" & Me.txtbilsampel & "'" & ",LabelSampelkod='" & Me.txtlabel & "'" & ",JenisAnalisis1kod='" & Me.cboanalisis1 & "'" & _
",JenisAnalisis2kod='" & Me.cboanalisis2 & "'" & ",JenisAnalisis3kod='" & Me.cboanalisis3 & "'" & ",JenisAnalisis4kod='" & Me.cboanalisis4 & "'" & ",JenisAnalisis5kod='" & Me.cboanalisis5 & "'" & _
",JenisAnalisis6kod='" & Me.cboanalisis6 & "'" & ",JenisAnalisis7kod='" & Me.cboanalisis7 & "'" & ",JenisAnalisis8kod='" & Me.cboanalisis8 & "'" & ",Unit1kod='" & Me.cbounit1 & "'" & ",Unit2kod='" & Me.cbounit2 & "'" & ",Unit3kod='" & Me.cbounit3 & "'" & ",Unit4kod='" & Me.cbounit4 & "'" & ",Unit5kod='" & Me.cbounit5 & "'" & ",Unit6kod='" & Me.cbounit6 & "'" & ",Unit7kod='" & Me.cbounit7 & "'" & ",Unit8kod='" & Me.cbounit8 & "'" & _
",Masakod='" & Me.txtmasa & "'" & _
",KosAnalisiskod='" & Me.txtkos & "'" & ",NoGerankod='" & Me.txtnogeran & "'" & ",StatusPembayarankod='" & Me.cbostatuspembayaran & "'" & ",NamaInstitusiLuarUKMkod='" & Me.txtnamainstitusi & "'" & _
",AlamatLuarUKMkod='" & Me.txtalamatluar & "'" & ",Catatankod='" & Me.txtcatatan & "'" & ",Masa1kod='" & Me.Masa1 & "'" & ",Masa2kod='" & Me.Masa2 & "'" & ",Masa3kod='" & Me.Masa3 & "'" & ",Masa4kod='" & Me.Masa4 & "'" & ",Masa5kod='" & Me.Masa5 & "'" & ",Masa6kod='" & Me.Masa6 & "'" & ",Masa7kod='" & Me.Masa7 & "'" & ",Masa8kod='" & Me.Masa8 & "'" & _
",TextHarga1kod='" & Me.TextHarga1 & "'" & ",TextHarga2kod='" & Me.TextHarga2 & "'" & ",TextHarga3kod='" & Me.TextHarga3 & "'" & ",TextHarga4kod='" & Me.TextHarga4 & "'" & ",TextHarga5kod='" & Me.TextHarga5 & "'" & ",TextHarga6kod='" & Me.TextHarga6 & "'" & ",TextHarga7kod='" & Me.TextHarga7 & "'" & ",TextHarga8kod='" & Me.TextHarga8 & "'" & ",Total1kod='" & Me.Total1 & "'" & ",Total2kod='" & Me.Total2 & "'" & ",Total3kod='" & Me.Total3 & "'" & ",Total4kod='" & Me.Total4 & "'" & ",Total5kod='" & Me.Total5 & "'" & ",Total6kod='" & Me.Total6 & "'" & ",Total7kod='" & Me.Total7 & "'" & ",Total8kod='" & Me.Total8 & "'" & ",Totaltestkod='" & Me.Totaltest & "'" & _
"WHERE ID= " & Me.Januarisub.Form.Recordset.Fields("ID")

cmdClear_Click




End If


'clear form

'refresh data in list on form
Januarisub.Form.Requery

End Sub

 

[cikwan82]

What i want to do is before my data jump into table januari when i click button ADD, some notice will pop up if my "NoGerankod" or "NoMatrikkod" info have same number listed in my table "HutangKeseluruhan". please guide me.

 

[Galaxiom]

ElseIf is VBA.

If x = y Then
    do something
ElseIf a = b Then
   do something else
Else
   another different thing
End If

What I meant was could you please explain this unusual syntax. What is the & ""="" about and why are you using the Tag?
If Me.txtidborang.Tag & "" = "" = whatever

Also as I said earlier, a table called Januari strongly suggests the structure of your data is not what it should be. The repeating groups of eight fields in the table confirms this data structure is wrong.

Complex expressions often occur because of structural problems. I strongly recommend you reconsider your table design before you continue. Otherwise you are going to be making hard work for yourself.

 

[cikwan82]

ok.. i got it.. thank you , i will follow your advice...